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

=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" <(E-Mail Removed)> wrote in message
news:98217347-45F1-4F14-BC77-(E-Mail Removed)...
>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

Hi Ryan,

Look here:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ryan_Keys" <(E-Mail Removed)> wrote in message news:98217347-45F1-4F14-BC77-(E-Mail Removed)...
|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

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" <(E-Mail Removed)> wrote in message
> news:98217347-45F1-4F14-BC77-(E-Mail Removed)...
> >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
>
>
>