Prorated vacation formula required

[Guest]

Appreciate the calculation for vacation accural

Vacation is prorated.
Less than 5 years of service entitled to 15 days (1.25 days per month)
5-10 years of service entitled to 20 days (1.67 days per month)
10+ years of service entitled to 23 days (1.92 days per month)

Date is cell B4
Result in B5

Thank you

 

[JE McGimpsey]

One way:

B5: =15 + 5 * (DATEDIF(B4, TODAY(), "y") >= 5) + 3 * (DATEDIF(B4,
TODAY(), "y") >= 10)

 

[Guest]

Not quite what I'm looking for. This will provide what the annual
entitlement is however, what if someone starts 1/2 way in the 1st year? Also,
I'm looking to show what has been accured to date.

 

[JE McGimpsey]

Yes, it would be helpful if you gave all the details first.

The formula I gave assumed the start date was in B4 and that a "year of
service" was based on that start date. That would make the "first year"
the year up to the first anniversary of that date, right?

Or does accrual start only at some calendar anniversary date (e.g., 1
January)?

What *should* happen if "someone starts 1/2 way in the 1st year" (or day
2 (or day 365) of "the first year"? Do they get nothing?

What rules affect accrual - does the accrued time roll over?

Does it roll over at the anniversary date or the calendar anniversary
date?

Is there a maximum that rolls over? or a maximum total accrual?

 

[Guest]

Sorry if I was unclear

Vacation entitlement begins immediately. Therefore, 0-5 years, employee is
earning 1.25 days per month. So if someone starts Jan 1st of this year they
have already earned 3.75. If employee started Feb 15, they have earned 2.5.

You are correct that year of service based on B4 (start date)

Roll over at calendar date (Dec. 31st)

Hope this helps you help me.