Calculating Vacation Time at Beginning of Year

ctiger

New member
Joined
Nov 4, 2013
Messages
16
Reaction score
0
Points
0
Excel Version(s)
2013
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 DateVacation Time
1/1/199480
8/20/20130
8/20/200840

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
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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
Joined
Nov 4, 2013
Messages
16
Reaction score
0
Points
0
Excel Version(s)
2013
Works great, using the second formula, thanks so much for the help!
 
Top