Date formula resulting in Year, Months & days

[Ryan_Keys]

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

 

[Bob Phillips]

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

 

[Niek Otten]

Hi Ryan,

Look here:

http://www.cpearson.com/excel/datedif.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|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

 

[Ryan_Keys]

Brilliant - Just what I needed thanks a bunch!

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