Vacation Entitlement based on full years of service and odd vacation year

So close, both of you! You are amazing.

Yes, you are reading that correctly daddylonglegs.

I did a bit of testing with both of your formulas and got the full years (under 5 years, less than 10 years, etc) working properly. There was a bit of tweaking, although I can't explain why. I had to change {1,4,10,18} to {1,5,11,19} in both of your formulas.

ben_hensel

=IF($B$4 - C9>395, INDEX({10,15,20,25}, MATCH(($B$4 - C9) /365.25, {1,5,11,19}, 1)), MIN( INT(IF($B$4>C9, $B$4 - C9, C9 - $B$4) /30), 10))

daddylonglegs

=IFERROR(LOOKUP(DATEDIF(C10,B4,"y"),{1,5,11,19;10,15,20,25}),MIN(10,DATEDIF(C10,B4,"m")))

Where both have trouble is under 10 months. If I set "today's" date (C2) to May 1, 2017, and set the hire date for the same day, the entitlement shows 10 days. I can get the entitlement days to go down to zero if I set the hire date 11 months and 1 day into the "future" past whatever C2 is set at.


See attached for example..