Subtracting dates

[skeep]

If I start at 1/1/04 and end at 1/1/05 for example, how can I make a formula that tells me either months past or years past

Thanks

 

[Frank Kabel]

Hi
something like
=YEAR(A1)=YEAR(A2)
or
MONTH(A1)=MONTH(A2)

 

[Guest]

Hi

My solution would be -

Eg.
Date A - 1-Jan-2004
Date B - 25-Sep-2007

Years Difference = year(Date A) - year(Date B)
Months Difference = month(Date A) - month(Date B)
Days Difference = day(Date A) - day(Date B)

Result - 3 years, 8 months and 24 days

Cheers
Paddy

-----Original Message-----
If I start at 1/1/04 and end at 1/1/05 for example, how

can I make a formula that tells me either months past or
years past.

 

[macropod]

Hi Skeep,

You could use a macro like:

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

Or a formulae like:
=DATEDIF(A1,A2,"m")&" years, "&DATEDIF(A1,A2,"ym")&" months,
"&DATEDIF(A1,A2,"md")&" days."
where you would need to decrement the start date by 1 day (or increment the
end date by 1 day) if both the start & end dates are to be included.

Cheers

If I start at 1/1/04 and end at 1/1/05 for example, how can I make a

formula that tells me either months past or years past.

 

[Ron Rosenfeld]

If I start at 1/1/04 and end at 1/1/05 for example, how can I make a formula that tells me either months past or years past.

Thanks

Try the DateDif function. It is undocumented in most versions of Excel, but
present. Documentation is at http://www.cpearson.com/excel/datedif.htm


--ron