Calculating vacation entitlement and prorated amounts

[Chrissy1911]

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,

 

[NateSC]

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

 

[Chrissy1911]

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

View attachment 89648

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?

 

[NateSC]

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?

 

[Chrissy1911]

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?

 

[NateSC]

That's the right thing to do. Here is mine with C3:

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

 

[Chrissy1911]

That's the right thing to do. Here is mine with C3:

=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

 

[NateSC]

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!

 

[Chrissy1911]

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