# Calculating Vacation Time at Beginning of Year

#### ctiger

##### New member
I have a formula that will calculate the vacation time earned based on hire date. The amount of time changes based on years of service.
 Hire Date Vacation Time 1/1/1994 80 8/20/2013 0 8/20/2008 40

The formula works great - =LOOKUP(DATEDIF(\$C3,TODAY(),"Y"),{0,1,5,9,10},{0,80,40,40,80}). My problem now is that the policy changed and the time earned BEGINS at the start of the new year, so for example the person hired on 8/20/2008 would not earn their time until 1/1/2009. How can I add that part to my current formula? Thanks Gene

#### NBVC

##### Super Moderator
Staff member
Assuming that for the first Hire date, you want to count from that date since it is on the first, then

=IFERROR(LOOKUP(DATEDIF(DATE(YEAR(\$C3)+(TEXT(\$C3,"d/m")<>"1/1"),1,1),TODAY(),"Y"),{0,1,5,9,10},{0,80,40,40,80}),0)

if you still want to go to next year..

=IFERROR(LOOKUP(DATEDIF(DATE(YEAR(\$C3)+1,1,1),TODAY(),"Y"),{0,1,5,9,10},{0,80,40,40,80}),0)

#### ctiger

##### New member
Works great, using the second formula, thanks so much for the help!