Calculating vacation entitlement and prorated amounts

Chrissy1911

New Member
Joined
Apr 12, 2023
Messages
8
Office Version
  1. 365
Platform
  1. Windows
I need to calculate vacation with the following parameters:
0-6 years 15 days
7-19 years 20 days
20 + years 25 days

On Jan 1 each year the entitlement based on years of service is awarded for the current year, for example Jan 1/2023 you have 15 days in your accrual to use in 2023. Your first year worked is pro-rated based on start date.

I would like to know how much each employee has accrued at todays date from Jan 1 based on their entitlement.

Thanks,
 
This is what I get. Which part is wrong? Do you get the same?

1681415850702.png
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
No, I don't get any that produce 7.083333, the cell in the formula you wrote has C2 in it. Is that for the employee start date?
Yes. In my sheet I had the start dates in Column C starting in cell C2. How is your sheet set up? What column has the start dates and which row does it start on?
 
Upvote 0
Yes. In my sheet I had the start dates in Column C starting in cell C2. How is your sheet set up? What column has the start dates and which row does it start on?
My start dates are in column C starting in row 3. I changed all the C2's in your formula to C3, is there one that I shouldn't have changed?
 
Upvote 0
That's the right thing to do. Here is mine with C3:
Excel Formula:
=IFS((YEAR(TODAY())-YEAR(C3))<1,ROUND(YEARFRAC(DATE(YEAR(TODAY()),12,31),C3)*15*(TODAY()-C3)/(DATE(YEAR(TODAY()),12,31)-C3),0),(YEAR(TODAY())-YEAR(C3)-1)<6,15*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()),(YEAR(TODAY())-YEAR(C3)-1)<20,20*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()),TRUE,25*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()))

Is it people that started in 2003 that are incorrect? If so, I know why. They haven't hit the January after their 20th anniversary. That's how it is done here, but that could have been a bad assumption.

1681419312219.png
 
Last edited:
Upvote 0
That's the right thing to do. Here is mine with C3:
Excel Formula:
=IFS((YEAR(TODAY())-YEAR(C3))<1,ROUND(YEARFRAC(DATE(YEAR(TODAY()),12,31),C3)*15*(TODAY()-C3)/(DATE(YEAR(TODAY()),12,31)-C3),0),(YEAR(TODAY())-YEAR(C3)-1)<6,15*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()),(YEAR(TODAY())-YEAR(C3)-1)<20,20*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()),TRUE,25*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()))

Is it people that started in 2003 that are incorrect? If so, I know why. They haven't hit the January after their 20th anniversary. That's how it is done here, but that could have been a bad assumption.

View attachment 89651
YES, that is who it is. ofcourse I'm going to have some other issues when we have people that were grandfathered into or negotiated more vacation from the start. MAN, why do things have to be so difficult, can't everyone be the same? LOL
 
Upvote 0
If you need it to behave differently so that it gives them the extra days in the same year as when they will hit the anniversary, we can make that adjustment.

I would lean towards making a column for the number of days they get each year and then calculate the accrual in a separate column. Then you can just overwrite the special cases and make them a color so you remember to adjust them when they hit a new anniversary. I can split the equations to do this if needed.

Good luck!
 
Upvote 0
If you need it to behave differently so that it gives them the extra days in the same year as when they will hit the anniversary, we can make that adjustment.

I would lean towards making a column for the number of days they get each year and then calculate the accrual in a separate column. Then you can just overwrite the special cases and make them a color so you remember to adjust them when they hit a new anniversary. I can split the equations to do this if needed.

Good luck!
Thanks for your help :)
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top