How to make a Vacation accrual template in Excel

[Hache hi]

I am working on a spread sheet for my Company. I have looked at many examples but I am still not finding what I need with the specific perimeters involved. the facts:

• PDO accrual rate increases every employee anniversary.
• PDO accrues every weekly pay period worked.
• PDO accrues after 90 days of employment.
• PDO max accrual is 120 hours.

PDO incremental rate accrual as follows:

• 1st year----2.0 hrs per pay period
• after first year-----2.15
• after second yr----2.3
• after third year----2.46
• after fourth year--2.61
• after fifth year-----2.76
• after sixth year----2.92
• after seventh yr---3.07
• after eigth yr------3.23

[Panop]

There is a short formula which I want to refer here. This can help you to get what you are looking for =IF(TODAY()-D5<90,0,MIN(120,DATEDIF(D5,TODAY(),"M")*CHOOSE(SUM PRODUCT(--(DATEDIF(D5,TODAY(),"M")>{0;1;2;3}*12)),2,2.15,2.3,2.46,2.61,2.76,2.92,3.07,3.23)))

[Hache hi]

I copied it into a spreadsheet but im not sure how the cells should be set up to read to get the result. if I could impose upon you good nature. In my tests, I just set the employee's start date in D5, and then plugged in that formula. Perhaps the 120 is because they have been there long enough to accrue the max, 120.