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]

I had start dates in Column C. You will want to adjust for your spreadsheet, but this seems to work:

=IFS((YEAR(TODAY())-YEAR(C2)-1)<1,ROUND(YEARFRAC(DATE(YEAR(TODAY()),12,31),C2)*15,0),(YEAR(TODAY())-YEAR(C2)-1)<6,15,(YEAR(TODAY())-YEAR(C2)-1)<20,20,TRUE,25)

.
You may choose to ROUNDDOWN instead of ROUND for the current year depending on how you want to deal with fractions.

 

[Chrissy1911]

I had start dates in Column C. You will want to adjust for your spreadsheet, but this seems to work:

=IFS((YEAR(TODAY())-YEAR(C2)-1)<1,ROUND(YEARFRAC(DATE(YEAR(TODAY()),12,31),C2)*15,0),(YEAR(TODAY())-YEAR(C2)-1)<6,15,(YEAR(TODAY())-YEAR(C2)-1)<20,20,TRUE,25)

.
You may choose to ROUNDDOWN instead of ROUND for the current year depending on how you want to deal with fractions.

Thank you very much, this returns just 15, 20 or 25 which is what I need in 1 column so I know but I was looking for a formula that would give me what they've accrued from Jan 1/23 to date just in case they left throughout the year then I would have an up to date accrual.

 

[NateSC]

Sorry, I misunderstood and gave you prorated days for Year 1 depending on starting date, but not accrued days this year.
If you already have the column with the correct number of days per year, this equation could be simplified. But just in case you don't, the whole thing with rounding would be:

=ROUND(IFS((YEAR(TODAY())-YEAR(C2)-1)<1,ROUND(YEARFRAC(DATE(YEAR(TODAY()),12,31),C2)*15,0),(YEAR(TODAY())-YEAR(C2)-1)<6,15,(YEAR(TODAY())-YEAR(C2)-1)<20,20,TRUE,25)*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()),0)

Remember to change C2 to your start date location and adjust the rounding depending on the results you need (you may need roundup or rounddown instead of round).

 

[Mario_R]

Hi.
If the start date is in cell A2 and you are calculating aging from 1/1/2023, the following may help:

=INDEX( {25;20;15}, MATCH( A2, EDATE("2023/1/1", -{600;240;84} ) ) )

 

[Mario_R]

And you can also try:

=INDEX( {25,20,15}, MATCH( A2, EDATE("2023/1/1", -{600,240,84} ) ) )

 

[Chrissy1911]

Sorry, I misunderstood and gave you prorated days for Year 1 depending on starting date, but not accrued days this year.
If you already have the column with the correct number of days per year, this equation could be simplified. But just in case you don't, the whole thing with rounding would be:

=ROUND(IFS((YEAR(TODAY())-YEAR(C2)-1)<1,ROUND(YEARFRAC(DATE(YEAR(TODAY()),12,31),C2)*15,0),(YEAR(TODAY())-YEAR(C2)-1)<6,15,(YEAR(TODAY())-YEAR(C2)-1)<20,20,TRUE,25)*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()),0)

Remember to change C2 to your start date location and adjust the rounding depending on the results you need (you may need roundup or rounddown instead of round).

Good Morning, this formula seems to work with rounding, thank you so much I'm no good when the formulas need to be this complicated so I appreciate your help. How can I remove the rounding and see the exact number? I tried to read the formula and remove the round but I kept getting errors.

 

[Mario_R]

Chrissy1911:

Did you try what was suggested in post #5 or #6?...

 

[NateSC]

I found a mistake when doing some testing that blew up any start dates in 2022 or 2023. Below are the corrected versions.
Without rounding:

=IFS((YEAR(TODAY())-YEAR(C2))<1,ROUND(YEARFRAC(DATE(YEAR(TODAY()),12,31),C2)*15*(TODAY()-C2)/(DATE(YEAR(TODAY()),12,31)-C2),0),(YEAR(TODAY())-YEAR(C2)-1)<6,15*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()),(YEAR(TODAY())-YEAR(C2)-1)<20,20*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()),TRUE,25*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()))

With rounding:

=ROUND(IFS((YEAR(TODAY())-YEAR(C2))<1,ROUND(YEARFRAC(DATE(YEAR(TODAY()),12,31),C2)*15*(TODAY()-C2)/(DATE(YEAR(TODAY()),12,31)-C2),0),(YEAR(TODAY())-YEAR(C2)-1)<6,15*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()),(YEAR(TODAY())-YEAR(C2)-1)<20,20*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()),TRUE,25*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY())),0)

 

[Chrissy1911]

I found a mistake when doing some testing that blew up any start dates in 2022 or 2023. Below are the corrected versions.
Without rounding:

=IFS((YEAR(TODAY())-YEAR(C2))<1,ROUND(YEARFRAC(DATE(YEAR(TODAY()),12,31),C2)*15*(TODAY()-C2)/(DATE(YEAR(TODAY()),12,31)-C2),0),(YEAR(TODAY())-YEAR(C2)-1)<6,15*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()),(YEAR(TODAY())-YEAR(C2)-1)<20,20*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()),TRUE,25*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()))

With rounding:

=ROUND(IFS((YEAR(TODAY())-YEAR(C2))<1,ROUND(YEARFRAC(DATE(YEAR(TODAY()),12,31),C2)*15*(TODAY()-C2)/(DATE(YEAR(TODAY()),12,31)-C2),0),(YEAR(TODAY())-YEAR(C2)-1)<6,15*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()),(YEAR(TODAY())-YEAR(C2)-1)<20,20*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()),TRUE,25*YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY())),0)

Thank you so much, it's calculating 4.25 days for the 15 day people, 5.67 for the 20 day people and 5.67 for the 25 day people but the 25 day people should be 7.??. Again, I tried to read the formula and see if there was a quick change where maybe something was keyed as 20 instead of 25 but I was lost. I also didn't want to mess up what you already had there because most of it is resulting in exactly what I need. Is it a quick fix that I can do?