Hello,
I have been asked to adjust our vacation accrual method from Calendar Year to Anniversary Year. Our employees currently accrue one (1)vacation day per month up to 10 days per year for the first two years of employment.
On the employees second year anniversary, they begin to accrue (1.5) vacation days per month up to 15 days per year.
The challenge is how to calculate the change during the year rather than at the end, since we do not match two months out of the year.
I tried the 2 formulas below, but would like to know if I am on the right track and which is the preferred method or if not, can someone recommend a more common method.
Formula 1:
-10 days/12 months = .8333 vacation days per month for the first two years of employment
-15 days/12 months = 1.25 vacation days per month a 2nd anniversary
Therefore an employee with a 3/1 anniversary date would be calculated .8333 x 2 = 1.67 days and 1.25 x 10 = 12.5 for a total of 14.17 days during that year.
Formula 2:
Vacation days accrued per month prior to an anniversary adjustment, plus vacation days accrued per month after an anniversary adjustment, less the last 2 months of the year.
(ie: an employee with a 3/1 anniversary date.
-1 vac day x 2 mths = 2 accrued vac days
-1.5 vac days x 8 mths = 12 accrued vac days
For a total of 14 vacation days)
I would appreciate guidance and if available in an Excel spreadsheet that would greatly appreciated as well.
Thank you!