Problem with function

[Mike]

Hi
I am trying to make a holiday sheet , every employee gets 20 days holidays
per year, but if they have worked for us for more than 15 years then they get
an extra day, and another extra day for more than 20 years service, i have a
start date for each employee, how can i get excell to give me the number of
days each employee is entitled to please

 

[Gary''s Student]

Let's assume that the name is in column A and the Service Date is in column B

=20+(DATE(YEAR(B1)+15,MONTH(B1),DAY(B1))<TODAY())+(DATE(YEAR(B1)+20,MONTH(B1),DAY(B1))<TODAY())

for example:

maxwell 7/25/2002 20
fred 2/5/1992 21
albert 2/6/1900 22

 

[Mike]

Thanks Gary that works brilliant,
The year start date is in another cell and the length of service is years
worked from start date to that date, can i adjust that formula so it
calculates length of service from start date to year start date rather that
today

 

[Max]

..The year start date is in another cell and the length of service is years
worked from start date to that date, can i adjust that formula so it
calculates length of service from start date to year start date rather that
today

Another play ..

Assuming startdates in B2 down,
Put in say, C2:
=VLOOKUP(DATEDIF(B2,--"1-Jan-2007","y"),{1,20;15,21;20,22},2)
Copy down

 

[Gary''s Student]

Hi Mike:

In place of:

TODAY()

use:

DATEVALUE("1/1/2007")

 

[Mike]

Brilliant thanks
--
thanks

Another play ..

Assuming startdates in B2 down,
Put in say, C2:
=VLOOKUP(DATEDIF(B2,--"1-Jan-2007","y"),{1,20;15,21;20,22},2)
Copy down

 

[Mike]

Brilliant Thanks
--
thanks

Hi Mike:

In place of:

TODAY()

use:

DATEVALUE("1/1/2007")

 

[Gary''s Student]

By the way:

=DATEVALUE("1/1/2007")

is a good year start date for this year

=DATEVALUE("1/1/"&YEAR(TODAY()))

is a good start date for the current year

 

[Max]

welcome ..