Prev: Looking for the sample file
Next: VLookup between date ranges ?
From: Newfie809 on 23 Apr 2010 11:35
If the formula for number of years is =Year(latest date)-Year(earlier
date)
this formula only show the nubmer of years, and I would like it to show the
number of years and months. I need help with this formula:
> >
> > A B C
> > Year Year Service
> > 01-Sep-89 31-Dec-09= 20 Years

It should be 20 years 4 Months.

(Remembering that teachers only work from September 1, to June 30, of each
year a total of 194 days.

From September 1, 1989 to June 30, 1990 = 1 Year of Service.
I know it only 10 months, but that is their year.

Is there a formula that could caculate the number of years and months.
--
Newfie
From: Jim Thomlinson on 23 Apr 2010 11:48
Check out this link...

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

Jim Thomlinson


"Newfie809" wrote:

> If the formula for number of years is =Year(latest date)-Year(earlier
> date)
> this formula only show the nubmer of years, and I would like it to show the
> number of years and months. I need help with this formula:
> > >
> > > A B C
> > > Year Year Service
> > > 01-Sep-89 31-Dec-09= 20 Years
>
> It should be 20 years 4 Months.
>
> (Remembering that teachers only work from September 1, to June 30, of each
> year a total of 194 days.
>
> From September 1, 1989 to June 30, 1990 = 1 Year of Service.
> I know it only 10 months, but that is their year.
>
> Is there a formula that could caculate the number of years and months.
> --
> Newfie
From: David Heaton on 27 Apr 2010 00:49
On Apr 24, 1:35 am, Newfie809 <clarar...(a)hotmail.com> wrote:
> If the formula for number of years is     =Year(latest date)-Year(earlier
> date)
> this formula only show the nubmer of years, and I would like it to show the
> number of years and months.  I need help with this formula:
>
>
>
> > > A                        B             C
> > > Year                   Year           Service
> > > 01-Sep-89     31-Dec-09=      20 Years
>
> It should be 20 years 4 Months.
>
> (Remembering that teachers only work from September 1, to June 30, of each
> year a total of 194 days.
>
> From September 1, 1989 to June 30, 1990 = 1 Year of Service.    
> I know it only 10 months, but that is their year.              
>
> Is there a formula that could caculate the number of years and months.  
> --
> Newfie

You could try the DATEDIF excel formula
The syntax is

=DATEDIF(StartDate,EndDate,ReturnValue)

the ReturnValue should be one of the following

"y" -returns the number of years between the dates
"m" -returns the number of months between the dates
"d" -returns the number of days between the dates
"ym" -returns the number of months between the dates ignoring the
years
"yd" -returns the number of months between the dates ignoring the
years

for Example

A1=01/09/1989
A2=31/12/2009


=DATEDIF(A1,A2,'y") & "Years and " & DATEDIF(A1,A2,'ym") & "Months"
would return

20 Years and 3 Months

hth

David

 | 
Pages: 1
Prev: Looking for the sample file
Next: VLookup between date ranges ?