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

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?

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

=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?

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

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?