Could you explain this to me Bob? I don't quite understand it. The 80 and 40
confuse me.

Thanks

"Bob Phillips" wrote:

Use a formula like this

=80+IF(DATEDIF(A1,TODAY(),"y")<6,0,IF(DATEDIF(A1,T ODAY(),"y")=6,ROUND(40*(DATE(YEAR(Hiredate)+1,0,0)-Hiredate)/(DATE(YEAR(Hiredate)+1,0,0)-DATE(YEAR(Hiredate),0,0)),0),40))

This caters for yesr 6, just add additional IFs for years 13, 19 and 24

Might be a good idea to put these formula

=DATEDIF(A1,TODAY(),"y")

=DATE(YEAR(Hiredate)+1,0,0)

=DATE(YEAR(Hiredate),0,0)

and refernce those cells in the formula, it will be more readable.


HTH

Bob

"RM270" wrote in message
...
Hi. I have an employee that was hired on 4-1-2003.
I use =DATEDIF(E64,TODAY(),"y") to determin that he has worked 6 yrs. When
he reaches his anniversay date, his vacation time will go from 80 hrs to
120
hrs. I use VLookup to determine the vacation time. This all works okay.

The problem is that on January 1, my business posts a list of vacation
allowances for the year. The list shows how much vacation time the
employee
gets, regardless of when the anniversay date is. So my above employee
will
show that he has 120 hrs of vacation time on January 1. This is only a
problem when the employee is in a year where vacation time changes from
one
level to the next.
Vacation time changes when the employee goes from year 6 to 7, year 13 to
14, year 19 to 20 and year 24 to 25. Can anyone help make this work
during
those years?

Thanks for any help.


.