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

[Roberta Walker]

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?

 

[Stefi]

=DATE(YEAR(DATE(YEAR(B1)+4,MONTH(B1),DAY(B1))),12,31)+1
Regards,
Stefi


„Roberta Walker†ezt írta:

 

[Sandy Mann]

Assuming that the maximum additional days holiday you get is 5, (if not
remove the MIN() part), try:

=25+(MIN(INT(DATEDIF(DATE(YEAR(B1)+1,1,1),TODAY(),"y")/4),5))

And format the cell a general or Number
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk