Vacation Accrual

[kanolan]

I use Excel 2000 and need some help creating a spreadsheet that will calculate accrued vacation time daily based on date of hire as well as time taken and balance remaining. I have searched the previous posts, but have not found one that suits the criteria that we need.

We have approximately 45-50 employees and the weekly accrual rates are:

One week for first year (.769)
Two weeks for years 2-5 (1.538)
Three weeks for years 5+ (2.307)

I’ve started (& attached) what I think would be a good way to track this, but don’t know how to write the formulas to calculate the values we need. Any help would be greatly appreciated because I’m ready to pull my hair out! Thanks in advance!

[foxguy]

I modified your file to show you a possible solution.

I highligted all the cells that I changed.

Note the current accrual rate is not used in any formula, it's just for information only.