Vacation Accrual Help Please

**meric327** · 05-08-2013, 02:02 PM

I am having difficulty writing a vacation accrual formula.

Basically I want the formula to start accruing from an employees hire date. If I use a Today() function I want to know how many hours an employee has accrued
0-1 years - .77 week accrual
2-9 years – 1.54 week accrual
10-14 years – 2.31 week accrual
15+ Years – 3.08 week accrual.
The employee should jump to the next level of accrual when their anniversary date hits and carry the total accrued from before anniversary date added in.

For example if my employee was hired 01/01/2012 and today was 01/08/2013 then employee would have accrued (.77*52) + (1.54*1) = 41.58 Hours

**loginjmor** · 05-08-2013, 03:22 PM

Hi -

Try the attached spreadsheet.

Hope that helps.

**daddylonglegs** · 05-08-2013, 03:47 PM

If you have start date in A2 and today's date in B2 then this formula will give you the total hours accrued

=(SUMPRODUCT((DATEDIF(A2,B2,"y")>{0,1,9,14})*52,DATEDIF(A2,B2,"y")-{0,1,9,14})+MATCH(DATEDIF(A2,B2,"y"),{0,1,9,14})*INT(DATEDIF(A2,B2,"yd")/7))*0.77

**meric327** · 05-08-2013, 04:07 PM

Thank you very much!

**meric327** · 05-08-2013, 04:17 PM

I have a question how do I account to accrue for current year only though but based on the rate by the years of service. For example if you are here 20+ you should start out accruing at 3.04

**meric327** · 05-08-2013, 04:21 PM

I have a question how do I account to accrue for current year only though but based on the rate by the years of service. For example if you are here 20+ you should start out accruing at 3.04