Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date formula resulting in Year, Months & days
I am trying to write a formula that calculates staff member's lenght of
service. In column E I have their start dates with E1 containing =today() eg. Row 3 has the following E3 = 24/08/2004 F3 has the formula =(YEAR($E$1)-YEAR(E3)) returning 4.00. I have checked the formating of decimal places & tried to use the ROUNDDOWN function without success. As they have not yet been here 4 years the result should be 3.85 - All of the results are rounding to the nearest whole number (0.6 is showing as 1.0) Ideally I would like column F to have number of full years then G to be full months (in the above example 10 mths) & then H be days (in above 6) so I have a complete number of years, months & days. Any help appreciated. Ryan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date formula resulting in Year, Months & days
=DATEDIF(E3,E1,"Y")
=DATEDIF(E3,E1,"YM") =DATEDIF(E3,E1,"MD") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ryan_Keys" wrote in message ... I am trying to write a formula that calculates staff member's lenght of service. In column E I have their start dates with E1 containing =today() eg. Row 3 has the following E3 = 24/08/2004 F3 has the formula =(YEAR($E$1)-YEAR(E3)) returning 4.00. I have checked the formating of decimal places & tried to use the ROUNDDOWN function without success. As they have not yet been here 4 years the result should be 3.85 - All of the results are rounding to the nearest whole number (0.6 is showing as 1.0) Ideally I would like column F to have number of full years then G to be full months (in the above example 10 mths) & then H be days (in above 6) so I have a complete number of years, months & days. Any help appreciated. Ryan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date formula resulting in Year, Months & days
Hi Ryan,
Look he http://www.cpearson.com/excel/datedif.aspx -- Kind regards, Niek Otten Microsoft MVP - Excel "Ryan_Keys" wrote in message ... |I am trying to write a formula that calculates staff member's lenght of | service. | | In column E I have their start dates with E1 containing =today() | eg. Row 3 has the following | E3 = 24/08/2004 F3 has the formula =(YEAR($E$1)-YEAR(E3)) returning 4.00. I | have checked the formating of decimal places & tried to use the ROUNDDOWN | function without success. As they have not yet been here 4 years the result | should be 3.85 - All of the results are rounding to the nearest whole number | (0.6 is showing as 1.0) | | Ideally I would like column F to have number of full years then G to be full | months (in the above example 10 mths) & then H be days (in above 6) so I | have a complete number of years, months & days. | | Any help appreciated. | | Ryan |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date formula resulting in Year, Months & days
Brilliant - Just what I needed thanks a bunch!
"Bob Phillips" wrote: =DATEDIF(E3,E1,"Y") =DATEDIF(E3,E1,"YM") =DATEDIF(E3,E1,"MD") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ryan_Keys" wrote in message ... I am trying to write a formula that calculates staff member's lenght of service. In column E I have their start dates with E1 containing =today() eg. Row 3 has the following E3 = 24/08/2004 F3 has the formula =(YEAR($E$1)-YEAR(E3)) returning 4.00. I have checked the formating of decimal places & tried to use the ROUNDDOWN function without success. As they have not yet been here 4 years the result should be 3.85 - All of the results are rounding to the nearest whole number (0.6 is showing as 1.0) Ideally I would like column F to have number of full years then G to be full months (in the above example 10 mths) & then H be days (in above 6) so I have a complete number of years, months & days. Any help appreciated. Ryan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add days to a date and return resulting date | Excel Worksheet Functions | |||
Year-Days-Months | Excel Worksheet Functions | |||
FORMULA FOR CALCULATING YEARS, DAYS, & MONTHS BETWEEN SEVERAL DATE | Excel Discussion (Misc queries) | |||
i have two days and i want the difference in days, months, year | Excel Worksheet Functions | |||
count number of months year to date | Excel Worksheet Functions |