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.
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
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