Solved Leave Calculation MS Access (1 Viewer)

Ganie

New member
Local time
Today, 13:37
Joined
Aug 25, 2015
Messages
27
Hi,
I am working on a project using Access 2013. I have a Leave table that calculate leave days,balance leave days,used leave days,
unpaid leave days. i have created it in excel and working fine. but i could not execute in access.
i hoping that someone cam help me. Thank's in advance.if you need the excel i will upload it.
 

Jon

Access World Site Owner
Staff member
Local time
Today, 20:37
Joined
Sep 28, 1999
Messages
7,305
Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum.

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We look forward to having you around here, learning stuff and having fun!
 

smtazulislam

Member
Local time
Today, 23:37
Joined
Mar 27, 2020
Messages
806
Check this one. Its my old Leave management.
 

Attachments

  • Leave Management Software Ver2.3.accdb
    660 KB · Views: 313

Ganie

New member
Local time
Today, 13:37
Joined
Aug 25, 2015
Messages
27
Check this one. Its my old Leave management.
Thank's. i will check if this fit to my need. newbie to access.
i have attach file file maybe you can review thank's in advance.
 

Attachments

  • Leave File.zip
    2 MB · Views: 277

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:37
Joined
Feb 28, 2001
Messages
27,001
@Ganie, your statement of the problem is so simple, so basic, that what you tried to do is actually a bit unclear.

When you say your Excel app can't be run in Access, either you didn't tell us what you did to convert the Excel app - or you don't recognize the differences between Excel and Access.

I don't know how to answer you except to ask you to clarify your question. What (more specifically) did you do to try to get this app to run in Access starting from Excel?

EDIT: When you posted the two Excel files in the ZIP, you have NO Access-usable files there. Did you try to directly execute an Excel workbook from Access? That won't work at all. Workbooks are NOT in Access format. Access format is totally and completely different from Excel format. They are not directly compatible.
 

Ganie

New member
Local time
Today, 13:37
Joined
Aug 25, 2015
Messages
27
Hi,

The_Doc_Man,​

thank you for your quick response, newbie here, regarding to my question, as you can see my excel attach, in the leave status you can see 3 sheets each sheets is connected to leave calculator and calendar in scenario im calculating the days of leave of employee like for instance employee will go leave starting 01.07.2021 to 31.07.2021 which is equal to 26 days. (Friday and holiday is not included )
I dont how access work with that scenario.

Employee leave per month is 2.5 days
And per year is 30 days.
Please refer to my formula in attach excel sheets.

Thanks in advance sir.
 

Attachments

  • Leave File.zip
    2 MB · Views: 247

Ganie

New member
Local time
Today, 13:37
Joined
Aug 25, 2015
Messages
27
and the second scenario is payment calculation, please refer to my formula no idea how it works in access.

Thanks in advance.
 

mike60smart

Registered User.
Local time
Today, 20:37
Joined
Aug 6, 2017
Messages
1,899
Thank's. i will check if this fit to my need. newbie to access.
i have attach file file maybe you can review thank's in advance.
Hi
This will give you a start
 

Attachments

  • holidays.zip
    283 KB · Views: 260

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:37
Joined
May 21, 2018
Messages
8,463
One trick in Access you can do is build a table of dates for say the next 20 years that has every date. Add fields for holiday and day of the week. Then you can use simple queries to get your counts for elapsed days minus weekends, Fridays, and holidays. Simple query for months and years worked. You can calculate these on the fly in code, but it is faster and easier to preload this table and use it for the calculations.
 

Ganie

New member
Local time
Today, 13:37
Joined
Aug 25, 2015
Messages
27
Hi
This will give you a start
Hi mike,

the idea is there but i need to exclude the Friday and holiday.

for example

Leave start 01.07.2021
Leave Ends 03.07.2021

the result should be = 2 days only not 3 days (2nd of July where friday it should be excluded, the same if there is holiday).

Example 2

Leave start 01.07.2021
Leave ends 05.07.2021 = Let say Date 2 is Friday and Date 4 is Holiday

The result should be = 3 days only Date 2 and Date 4 are exlcuded from the calculation.

Thank's.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:37
Joined
May 21, 2018
Messages
8,463
Never mind, I see you already have a calendar table. So you simply do a dcount where date is between start and end where type = workday.
 

mike60smart

Registered User.
Local time
Today, 20:37
Joined
Aug 6, 2017
Messages
1,899
Hi mike,

the idea is there but i need to exclude the Friday and holiday.

for example

Leave start 01.07.2021
Leave Ends 03.07.2021

the result should be = 2 days only not 3 days (2nd of July where friday it should be excluded, the same if there is holiday).

Example 2

Leave start 01.07.2021
Leave ends 05.07.2021 = Let say Date 2 is Friday and Date 4 is Holiday

The result should be = 3 days only Date 2 and Date 4 are exlcuded from the calculation.

Thank's.
Ho

OK so we need a Table that lists your Countries Standard Yearly Holidays.

We can then use a Function to calculate.

Can you send me a Lists of your Holidays?
 

Ganie

New member
Local time
Today, 13:37
Joined
Aug 25, 2015
Messages
27
Ho

OK so we need a Table that lists your Countries Standard Yearly Holidays.

We can then use a Function to calculate.

Can you send me a Lists of your Holidays?
ew Year's Day
Jan 1 – 3, 2021​
Kuwait National Day
Thu, Feb 25, 2021​
Kuwait Liberation Day
Fri, Feb 26, 2021​
Prophet's Ascension
Mar 10 – 11, 2021*​
Eid al-Fitr
May 12 – 16, 2021*​
Day of Arafat
Likely Jul 18 – 19, 2021*​
Eid al-Adha
Likely Jul 19 – 22, 2021*​
Islamic New Year
Likely Aug 8 – 9, 2021*​
Prophet's Birthday
Likely Oct 18 – 19, 2021*​

if you can check my excel table you can have the idea how it works using excel formula. but i could not make it in access.
 

mike60smart

Registered User.
Local time
Today, 20:37
Joined
Aug 6, 2017
Messages
1,899
ew Year's Day
Jan 1 – 3, 2021​
Kuwait National Day
Thu, Feb 25, 2021​
Kuwait Liberation Day
Fri, Feb 26, 2021​
Prophet's Ascension
Mar 10 – 11, 2021*​
Eid al-Fitr
May 12 – 16, 2021*​
Day of Arafat
Likely Jul 18 – 19, 2021*​
Eid al-Adha
Likely Jul 19 – 22, 2021*​
Islamic New Year
Likely Aug 8 – 9, 2021*​
Prophet's Birthday
Likely Oct 18 – 19, 2021*​

if you can check my excel table you can have the idea how it works using excel formula. but i could not make it in access.
Hi
OK so I added a Module which calculates days taken which excludes Holidays.

See the attached.
 

Attachments

  • holidays.zip
    325.6 KB · Views: 248

Ganie

New member
Local time
Today, 13:37
Joined
Aug 25, 2015
Messages
27
Hi
OK so I added a Module which calculates days taken which excludes Holidays.

See the attached.
Hi,

Thank's for the quick response, as i notice 2 days remove in a week right? which is saturday and sunday.
but in my case only friday need to exclude in a week so it means in a week i have 6 days not 5 days.

holiday is fine now.

i hope you will get the idea, sorry because really new to access.
 

mike60smart

Registered User.
Local time
Today, 20:37
Joined
Aug 6, 2017
Messages
1,899
Hi,

Thank's for the quick response, as i notice 2 days remove in a week right? which is saturday and sunday.
but in my case only friday need to exclude in a week so it means in a week i have 6 days not 5 days.

holiday is fine now.

i hope you will get the idea, sorry because really new to access.
So are you happy with the way Access calculates the Nr of Days Vacation taken?
 

Ganie

New member
Local time
Today, 13:37
Joined
Aug 25, 2015
Messages
27
So are you happy with the way Access calculates the Nr of Days Vacation taken?
Yeah but still having issue with fridays, because in your sample saturday and sunday are excluded right.?

and one more thing i forgot the leave entitlement
here the scenario
in a year employee are entitled for 30 Days leave (2.5 days/month)

Here my excel formula to get the leave entitlement
((Leave Start-JoiningDate+1)-Unpaid Leave = Length Service
Length of service/365*30 = Leave entitlement

and used leave will deduct to leave entitlement

Total Leave entitlement - Used leaves = Leave balance

here i attach the excel for your kind reference. thank's.
 

Attachments

  • Leave Status Sheet.zip
    1 MB · Views: 240

mike60smart

Registered User.
Local time
Today, 20:37
Joined
Aug 6, 2017
Messages
1,899
Hi Ganie

To be honest I do not know how to not include the Friday.

I will ask a question on the Forum
 

Ganie

New member
Local time
Today, 13:37
Joined
Aug 25, 2015
Messages
27
Hi, mike
Really appreciate your effort to help me.
It is possible to add all fridays in the holidays table (assuming that they are also holidays) so they will not included in the count of days?
Then saturday and sunday will include in the count of days. What do you think?
Thanks and God bless.
 

Users who are viewing this thread

Top Bottom