Problem with function
 

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
--
thanks

Mike

Problem with function
 

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

--
Gary''s Student - gsnu200730


"Mike" wrote:

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
--
thanks

Mike

Problem with function
 

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
--
thanks

Mike


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200730


"Mike" wrote:

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
--
thanks

Mike

Problem with function
 

"Mike" wrote:
..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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Problem with function
 

Hi Mike:

In place of:

TODAY()

use:

DATEVALUE("1/1/2007")
--
Gary''s Student - gsnu200730


"Mike" wrote:

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
--
thanks

Mike


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200730


"Mike" wrote:

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
--
thanks

Mike

Problem with function
 

Brilliant thanks
--
thanks


"Max" wrote:

"Mike" wrote:
..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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Problem with function
 

Brilliant Thanks
--
thanks


"Gary''s Student" wrote:

Hi Mike:

In place of:

TODAY()

use:

DATEVALUE("1/1/2007")
--
Gary''s Student - gsnu200730


"Mike" wrote:

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
--
thanks

Mike


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200730


"Mike" wrote:

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
--
thanks

Mike

Problem with function
 

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
--
Gary''s Student - gsnu200730


"Mike" wrote:

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
--
thanks

Mike


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200730


"Mike" wrote:

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
--
thanks

Mike

Problem with function
 

welcome ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mike" wrote in message
...
Brilliant thanks
--
thanks