Excel Formula for years and months

N

Newfie809

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:
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.
 
D

David Heaton

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:




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.  

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula Excel 2
Date formula rounded up 2
Month days over a time period 5
Value of the first day of a year in a list 5
If Function 1
Count partial dates 1
Leap Year 7
Summary of shipments by month 5

Top