Solved Leave Calculation MS Access (1 Viewer)

[mike60smart]

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.

I modified the Function and I think the calculation now works

 

[Ganie]

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.

 

[mike60smart]

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.

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 ?

 

[isladogs]

@Ganie
You have received a lot of help from several members at this forum. It is therefore rather disappointing to see you crossposting the same question at Leave Calculation MS Access - Microsoft Access / VBA (bytes.com)

Please read this article about why it matters Excelguru Help Site - A message to forum cross posters

 

[Ganie]

@Ganie
You have received a lot of help from several members at this forum. It is therefore rather disappointing to see you crossposting the same question at Leave Calculation MS Access - Microsoft Access / VBA (bytes.com)

Please read this article about why it matters Excelguru Help Site - A message to forum cross posters

Hi isladogs,
Thank's. for your suggestion. hoping that you can help me too. signing up.

 

[isladogs]

I'll leave you in the capable hands of others

 

[Ganie]

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 ?

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)

 

[mike60smart]

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)

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?

 

[Ganie]

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?

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.

 

[mike60smart]

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.

Hi
We are slowly getting what you need.
See the attached.

 

[Ganie]

Hi
We are slowly getting what you need.
See the attached.

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]

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).

and allso the unpaid leave history should add the total

 

[mike60smart]

and allso the unpaid leave history should add the total

See if this is correct

 

[Ganie]

See if this is correct

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.

 

[mike60smart]

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.

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

 

[Ganie]

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

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.

 

[mike60smart]

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.

Sorry but I am not willing to redo what I started.
My time is precious and I am not willing to just waste it

 

[Ganie]

Sorry but I am not willing to redo what I started.
My time is precious and I am not willing to just waste it

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.

 

[dragony2000]

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