| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with function
welcome ..
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mike" wrote in message ... Brilliant thanks -- thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem with IF function.... | Excel Worksheet Functions | |||
| Sum Function Problem!! | Excel Worksheet Functions | |||
| Problem with IF function | Excel Discussion (Misc queries) | |||
| Problem with IF function | Excel Worksheet Functions | |||
| IF function problem | Excel Worksheet Functions | |||
Linear Mode
