Vacation Accrual Formulas

[hikaramba]

10 days per year gives an accrual rate of 0.037736. The basic formula would be =(TODAY()-Date(Year(Today()),Month(AnniversaryDate),Day(AnniversaryDate))+1)*VLOOKUP(Year(Today()-AnniversaryDate)-1899,LookupTableOfDaysAccruedPerYear,2,True)+rollover. If you can zip and post your workbook, I can give exact cell references.

Can this formula be used for vacation time that is based on years of service for the accrual rate, but starts accruing on Jan 1 of every year with no rollover? For example, 6.67 hrs per month for years 1 thru 5 and 10 hours per month for over 5. Since an anniversary date may fall in the middle of the year the rate would change maybe in the middle of the year, so there would be a rate for so many months (i.e. 5 months at 6.67 hrs/mo) then the second rate for the rest of the year.

Thanks for the help and information.

[darkyam]

Here's a first attempt at this. I did it by day as by month makes the calculation more difficult (either an employee doesn't get hours until the end of the month and then gets them all or there would be some complex calculation that would probably be twice as long and more prone to error). Does this work?

[hikaramba]

Here's a first attempt at this. I did it by day as by month makes the calculation more difficult (either an employee doesn't get hours until the end of the month and then gets them all or there would be some complex calculation that would probably be twice as long and more prone to error). Does this work?

Oh my gosh! You were right on. Thank you so much. I have been working for days trying to work out a formula but couldn't get past the middle of the month thing. You have been such a great help.