Calculating number of Years

[Tuff]

Hi Guys,

Is there a function or formula I can use in Excel to calculate the number of years between two dates.
IE
Start Date = 01/01/99 End Date = 31/12/04.
I need the formula to tell me that is 5 years. (I am having a problem with the leap years).

 

[macropod]

Hi Tuff,

Caclulating the number of years, months and days between two dates isn't as straightforward as it might seem (as you've found)!

The following macro, which you could convert to a user-defined function expresses the period between two dates in years, months and days. Most of the work goes into calculating and returning the elapsed whole years - the elapsed months and days are just extras.

Sub CalcTerm()
Dim StartDate As Date, EndDate As Date, Include As String
Dim Years As Integer, Months As Integer, Days As Integer, DaysInMonth As Integer
On Error GoTo ExitSub
StartDate = InputBox("Enter the Start Date in dd/mm/yyyy format")
EndDate = InputBox("Enter the End Date in dd/mm/yyyy format")
Include = UCase(Left(InputBox("Include both the Start and End Dates? (Y/N)"), 1))
If (Month(EndDate) = 2) Then
DaysInMonth = 28 + (Month(EndDate) = 2) * ((Year(EndDate) Mod 4 = 0) + (Year(EndDate) Mod 400 = 0) - (Year(EndDate) Mod 100 = 0))
Else
DaysInMonth = 31 - (Month(EndDate) = 4) - (Month(EndDate) = 6) - (Month(EndDate) = 9) - (Month(EndDate) = 11)
End If
Years = Year(EndDate) - Year(StartDate) + (Month(EndDate) < Month(StartDate)) + (Month(EndDate) = Month(StartDate)) * (Day(EndDate) < Day(StartDate) + (Include = "Y"))
Months = (12 + Month(EndDate) - Month(StartDate) + (Day(EndDate) < Day(StartDate) + (Include = "Y"))) Mod 12
Days = (DaysInMonth + Day(EndDate) - Day(StartDate) - (Include = "Y")) Mod DaysInMonth
MsgBox "The term is " & Years & " year(s) " & Months & " month(s) " & Days & " day(s)."
ExitSub:
End Sub

If you wanted to use a formula for calculating just the elapsed years, you'd need to mimic this bit of code:
Years = Year(EndDate) - Year(StartDate) + (Month(EndDate) < Month(StartDate)) + (Month(EndDate) = Month(StartDate)) * (Day(EndDate) < Day(StartDate) + (Include = "Y"))
where (Include = "Y") is replaced by 0 to count only the end date or 1 to count both the start and end dates.

Cheers

 

[jonsi]

You really don't need to be this complex. Try the DATEDIF function

=DATEDIF(firstyear cell address,secondyear cell address,"y")

 

[TonyJollans]

Hi Tuff,

It really depends what answer you want in different circumstances, but DATEDIF will give you your 5 for your example.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[macropod]

Hi jonsi,

Evidently the worksheet DateDif function works differently than the vba DateDiff function. For example, in vba:
DateDiff("yyyy", "31/12/2000", "1/1/2001")
returns 1 (year) even though only one day has elapsed.

Cheers

 

[Tuff]

Thanks everyone,

What I have ended up using is to worksheet functions in conjuction with one another.

=(DATEDIF(firstyear cell address,secondyear cell address + 1,"Y"))+((DATEDIF(firstyear cell address,secondyear cell address + 1,"YD"))/365).

As the date range is not always an even year.

 

[macropod]

Hi Tuff,

FWIW, the 'leap year' phenomenum may still catch you out, where the second part of your expression could return 365/365 (=1)
Strictly speaking, the 'average year length is 265.2425 days, though this is more commonly expressed as 365.25 days. Using either of these divisors would avoid the above issue.

Cheers

 

[KenWright]

If you wanted Years / Months / Days and your data was in A1:A2 with earliest date being A2 then in any 3 cells:-

=DATEDIF(A2,A1,"y") will give you years

=DATEDIF(A2,A1,"ym") will give you the months

=DATEDIF(A2,A1,"md") will give you the days

eg 2 years, 4 months, 16 days

If just years are required then use just the first as Tony pointed out.

Regards
Ken................



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------