update figs from 1 Jan after 4th anniversary (2003)

Topic

New Reply

On 1st of Jan following the 4th anniversary of the date of joining our company you get 1 day extra holiday (25 to 26); 8th anniversary you get another (26 to 27); 12th anniversary you get a third (27 to 28)
Therefore, if I joined on 28/02/05 the 4th anniversary will be 28/02/09 my holiday entitlement will go up to 26 on 01/01/2010 to 27 on 01/01/2014 and to 28 on 01/01/2018.
My spreadsheet has joining dates in Col B and I would like to display correct current holiday entitlement in Col C
If it was just on the 4th, 8th or 12th anniversary of joining it would be fine – but I cannot work out how to automatically display the correct holiday entitlement from the following Jan 1st?

Thanks

Reply | Quote

Replies

Would you like to display the holiday entitlement for the current year? I.e. in your example, you’d see 25 today, but if it were July 2010, you’d see 26?
If so, you can use

=25+INT((YEAR(TODAY())-YEAR(B2)-1)/4)

in cell B2 and fill down.

Reply | Quote