Solved Leave Calculation MS Access

Ganie said:
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.
Click to expand...
I modified the Function and I think the calculation now works
 

Attachments

Hi mike,

How you doin, i have checked and included Fridays in Holidays table and remove Saturdays and Sundays in the module. i think is we are in a right track now. but i don't want duplicate. if the record is already there it should say duplicate entry is not allowed. and also if the entitlement is not enough the remaining days of leave will proceed to another record as unpaid.


example employee leave entitlement is only 20 days but he will go more than 20 days
here is the concept

let say employee apply leave from 01.08.2021 - 31.08.2021
since employee leave entitlement is only 20 days the annual leave calculation will stop on

What i am trying to do here is convert my excel payroll to access. i hope you get my idea. thank's and have a nice day. God bless.
 

Attachments

Ganie said:
Hi mike,

How you doin, i have checked and included Fridays in Holidays table and remove Saturdays and Sundays in the module. i think is we are in a right track now. but i don't want duplicate. if the record is already there it should say duplicate entry is not allowed. and also if the entitlement is not enough the remaining days of leave will proceed to another record as unpaid.


example employee leave entitlement is only 20 days but he will go more than 20 days
here is the concept

let say employee apply leave from 01.08.2021 - 31.08.2021
since employee leave entitlement is only 20 days the annual leave calculation will stop on

What i am trying to do here is convert my excel payroll to access. i hope you get my idea. thank's and have a nice day. God bless.
Click to expand...
Hi Ganie

Access is not Excel so we need to store data differently.

Your calculation for:- Length of Service = (Leave Start]-JoiningDate]+1)-[UnpaidLeave]

When you say Unpaid Leave is this the total days of Unpaid ?
 
mike60smart said:
Hi Ganie

Access is not Excel so we need to store data differently.

Your calculation for:- Length of Service = (Leave Start]-JoiningDate]+1)-[UnpaidLeave]

When you say Unpaid Leave is this the total days of Unpaid ?
Click to expand...
hi mike,

Thank's for the guidance and correction.

How about leave entitlement? sorry to ask you a lot of question. since i have no idea to store formulas in access.

When you say Unpaid Leave is this the total days of Unpaid ? (Yeah total unpaid leave)
 
Last edited:
Ganie said:
hi mike,

Thank's for the guidance and correction.

How about leave entitlement? sorry to ask you a lot of question. since i have no idea to store formulas in access.

When you say Unpaid Leave is this the total days of Unpaid ? (Yeah total unpaid leave)
Click to expand...
Hi Ganie

OK so if you have an Employee whose Joining Date is 8/8/2009
Takes Leave from 01 Mar 10 to 08 Mar 10

What would be their Length of Service?
 
mike60smart said:
Hi Ganie

OK so if you have an Employee whose Joining Date is 8/8/2009
Takes Leave from 01 Mar 10 to 08 Mar 10

What would be their Length of Service?
Click to expand...
Hi Mike,

Employee Length of service as of 01 Mar 10 is 206 Days.

Here my Formula with excel

Leave Start (01 Mar 2010) -Joining date (08.08.2009)+1 - Unpaid Leave (If any)

and the leave entitlement would be 16.9 Days.

Here is the formula for getting leave entitlement

Length of Service (206 Days)/365*30

I hope it helps thank's.
 
Ganie said:
Hi Mike,

Employee Length of service as of 01 Mar 10 is 206 Days.

Here my Formula with excel

Leave Start (01 Mar 2010) -Joining date (08.08.2009)+1 - Unpaid Leave (If any)

and the leave entitlement would be 16.9 Days.

Here is the formula for getting leave entitlement

Length of Service (206 Days)/365*30

I hope it helps thank's.
Click to expand...
Hi
We are slowly getting what you need.
See the attached.
 

Attachments

mike60smart said:
Hi
We are slowly getting what you need.
See the attached.
Click to expand...
Hi Mike,

yeah we are almost there only things i forgot to mentioned you.

1. Unpaid leave should include all in calculation example 12.07.2021-16.07.2021 = (5 days including Fridays and Holidays if any)
2. need to add another field for leave balance
here is the formula in excel

Leave entitlement - Total Leave days taken = _______

So if the employee ask how many remaining leave balance i can give him the details. (lets i will just type employee number (search and it will show the details for the employee itself e.g report).
 
Ganie said:
Hi Mike,

yeah we are almost there only things i forgot to mentioned you.

1. Unpaid leave should include all in calculation example 12.07.2021-16.07.2021 = (5 days including Fridays and Holidays if any)
2. need to add another field for leave balance
here is the formula in excel

Leave entitlement - Total Leave days taken = _______

So if the employee ask how many remaining leave balance i can give him the details. (lets i will just type employee number (search and it will show the details for the employee itself e.g report).
Click to expand...
and allso the unpaid leave history should add the total
 
mike60smart said:
See if this is correct
Click to expand...

1626108175958.png


Hi Mike,

The scenario should be like this.

If employee take a leave on July as shown in the Last annual leave Info which is 21 days only because there is 5 days holiday on that month.
21 days will be deducted to leave balance. so his leave balance will become 11.219 on august 01 2021. (Employee is 2.5 days each month/30 days per year)
if you got this idea then our problem near to reality. thanks.
 
Ganie said:
View attachment 92954

Hi Mike,

The scenario should be like this.

If employee take a leave on July as shown in the Last annual leave Info which is 21 days only because there is 5 days holiday on that month.
21 days will be deducted to leave balance. so his leave balance will become 11.219 on august 01 2021. (Employee is 2.5 days each month/30 days per year)
if you got this idea then our problem near to reality. thanks.
Click to expand...
Sorry but I will have to stop here as we are going backwards.

You have added random dates to the Holidays table?? Why did you do this?
It should ONLY contain a list of Actual Holiday Dates.

You also deleted quite a few lines from the NetWorkDays Function. Why?

Luck with your project
 
mike60smart said:
Sorry but I will have to stop here as we are going backwards.

You have added random dates to the Holidays table?? Why did you do this?
It should ONLY contain a list of Actual Holiday Dates.

You also deleted quite a few lines from the NetWorkDays Function. Why?

Luck with your project
Click to expand...
Sorry if i confused you.

You have added random dates to the Holidays table?? Why did you do this? the dates i have put in the Holidays table is all Fridays im just trying if will work. and i see that is working friday were excluded in the calculation please check and correct me if im wrong.

You also deleted quite a few lines from the NetWorkDays Function. Why? i just try to remove the saturdays and sundays and try if will work because i need them to be included in the count.

Sorry im just trying...
really appreaciate your effort to help me.
 
Ganie said:
Sorry if i confused you.

You have added random dates to the Holidays table?? Why did you do this? the dates i have put in the Holidays table is all Fridays im just trying if will work. and i see that is working friday were excluded in the calculation please check and correct me if im wrong.

You also deleted quite a few lines from the NetWorkDays Function. Why? i just try to remove the saturdays and sundays and try if will work because i need them to be included in the count.

Sorry im just trying...
really appreaciate your effort to help me.
Click to expand...
Sorry but I am not willing to redo what I started.
My time is precious and I am not willing to just waste it
 
mike60smart said:
Sorry but I am not willing to redo what I started.
My time is precious and I am not willing to just waste it
Click to expand...
Hi,

Sorry for that. i understand. take care and God bless.
again sorry and thank you for your time.
maybe i will stay with the excel seems the access is to complicated.
 
good day,

i will try to give hint about friday,
its easy way you can add clause that after calculation you sum fridays that excluded between the two dates.

thats what i get from your question
 
You must log in or register to reply here.

Similar threads

P
LEAVE MANAGEMENT
Replies
13
Views
804
Solo712
Solo712
W
Forecasting a date based on working days.
Replies
2
Views
205
Gasman
Gasman
S
Looking for a Reliable "Days Between" Calculator Tool
Replies
12
Views
910
Uncle Gizmo
Uncle Gizmo
T
If Statement with multiple condition
Replies
8
Views
256
cheekybuddha
cheekybuddha
X
When was my Worker's Last Day Off?
Replies
3
Views
351
MarkK
MarkK

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom