Solved Leave Calculation MS Access

[Ganie]

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]

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]

Check this one. Its my old Leave management.

 

[Ganie]

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.

 

[The_Doc_Man]

@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]

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.

 

[Ganie]

and the second scenario is payment calculation, please refer to my formula no idea how it works in access.

Thanks in advance.

 

[mike60smart]

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

 

[MajP]

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]

Hi
This will give you a start

thank's i will try and let you know.

 

[Ganie]

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]

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]

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]

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]

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.

 

[Ganie]

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]

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]

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.

 

[mike60smart]

Hi Ganie

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

I will ask a question on the Forum

 

[Ganie]

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.