Excel Formula for years and months
 

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

Excel Formula for years and months
 

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

Excel Formula for years and months
 

On Apr 24, 1:35*am, 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

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