Formula for Vacation Accrual Days Based on Anniversary Date

Hi Foxguy,

Thanks a lot!!! This looks great. A couple of quick questions for you...

In column I, it looks like the days accrued are compounding from columns F-H?
For example "Bob" who has been employeed for 2+ years just crossed his yearly anniversary date and should start accruing days from 5/3/10 - 5/23/10. Anything in the past should be zero. He is eligible for .55 days (20 days @ .03 per day).

Also, the vacation days are capped @ 10, 15, and 20 days per year. They should never have more than that during the course of the year.

I appreciate you helping me out!!!!!

If you can send back a revised spreadsheet that would be great.

Please let me know if you have any questions.

Do employees start accruing immediately or only after 1 year? If you use a table a little like foxguy suggested (I just changed the values in I7:I9) then you can use this formula in E5 copied down

=DATEDIF(B5,TODAY(),"yd")*D5/365

This doesn't need the column C values, just the hire dates in column B and the results from D. If accrual only starts after 1 year's service then you need an extra row in the table as per foxguy's version.

Note the above formula won't take you over the max amounts....and DATEDIF will give you a more accurate count of days than dividing by 365 due to leap years. See attached

Thanks Daddylonglegs this is exaclty what I am looking for... Greatly appreciated!!!!

One last question for you...

Would it be possible to create a formula in "Days Taken" off if I run a master report out of a database to pull only the days taken off during their anniversary interval...example below...

Jim took 2 days off. One in 1999 and the other in 2010. His anniversary year goes from 5/3/09 to 5/2/10 and then resets. I would want the # of days off of 1 to only pull to the consolidated tab within his anniversary interval. I would have a 2nd tab with the days off pulling to the "Day's Taken" section.

Employee Name Year Taken off Date Taken off # of Days
Cindy 2009 1/1/10 1
Bob 2008 4/1/08 0.5
John 2010 2/1/10 1
Joe 2006 4/1/06 0.5
Jim 1999 4/1/99 1
Jim 2010 5/17/10 1



Please let me know if you can send this last request back with a revised spreadsheet...Thanks so much for your help again

Days are accrued from hire date and start immediately. Anniversary date would be the year that the days start accruing again.

Foxguy, your correct. If his anniversary year goes from 5/3/09-5/2/10 and doesn't take any days, he looses 10 days and will start accruing again on 5/3/10. Then on 5/23 he would have .55 days available again accruing from 5/3/10-5/23/10.


Would it be possible to create a formula in "Days Taken" off if I run a master report out of a database to pull only the days taken off during their anniversary interval...example below...

Jim took 2 days off. One in 1999 and the other in 2010. His anniversary year goes from 5/3/09 to 5/2/10 and then resets. I would want the # of days off of 1 to only pull to the consolidated tab within his anniversary interval. I would have a 2nd tab with the days off pulling to the "Day's Taken" section.

Employee Name Year Taken off Date Taken off # of Days
Cindy 2009 1/1/10 1
Bob 2008 4/1/08 0.5
John 2010 2/1/10 1
Joe 2006 4/1/06 0.5
Jim 1999 4/1/99 1
Jim 2010 5/17/10 1

Thank you all for the assistance

All,

This is great!!!! Problem solved.

Thanks so much for everything and the help!!!!! Greatly appreciated