Date formula

[jody frazier]

I have a formula that talleys up vacation time for our employees.
=IF(DATEDIF(a2,
TODAY(),"Y")<10,DATEDIF(a2,TODAY(),"M")*3.30,400+DATEDIF(DATE(YEAR(a2)+10,MONTH(a2),DAY(a2)),TODAY(),"M")*6.60)

The policy states that an employee can start accruing more time per month
after he/she has had 10 years or more of service with us. My formula above
calculates this higher accrue rate on the employee's anniversary hire date.
I need it to start on the 10th calendar year not the anniversary hire date.
Can anyone help? I'm really stuck here... :-(

 

[bpeltzer]

My interpretation is that if someone started on 1/1/95, they'll start
accruing the higher rate on 1/1/05; if they started any other day in 1995,
they won't get the higher rate until 1/1/06. If that's correct, I think this
will get you there
=3.3*DATEDIF(A2,TODAY(),"M")+3.3*IF(TODAY()>=DATE(YEAR(A2-1)+11,1,1),DATEDIF(DATE(YEAR(A2-1)+11,1,1),TODAY(),"M"),0)
I kept the 3.3 you had, though I think 10/3 is probably what should be there
(the 3.3 causes a jump from 396 hours to 400 on the tenth anniversary).
Hope this helps. --Bruce