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?

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


€žRoberta Walker€� ezt Ã*rta:

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?

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


Replace @mailinator.com with @tiscali.co.uk


"Roberta Walker" wrote in message
...
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?