I am trying to create a vacation tracker for work. I have the following fields:
Hire Date (MM/DD/YYYY)
Total Time with Company in years (=(TODAY()-<Hire Date Field>)/365)
Vacation Hours Allotted
Vacation Hours Remaining (Calculated from the Allotted Field and a sum of all of the time sheets within the workbook)
I would like to have the Vacation Hours Alotted value dynamically reset to the appropriate value on the anniversary of the hire date posted, so employees can easily track "at a glance" how much time they have left before their vacation time resets. For example, if I was hired on February 1, I would like to be able to look at my vacation hours remaining field on January 1 and know how much time I have left so I can use it before I lose it.
Is there a way to use an IF Statement to do this? I already have an IF Statement in there that sets the hours on specific anniversary dates, but not for every year:
=IF(B2="","",INDEX({40,40,80,120,160}, MATCH(B2,{0,1,2,5,10})))
I would rather not have to set a massive IF statement to get my desired result, and I am not 100% comfortable with using a macro or VBA to achieve the desired result.
Bookmarks