Difference between two dates in Excel 2

[kabushnell]

I am trying to find the difference between two dates in Excel. I can find the number of months but I need the months and days. Basically, I need to see if the difference is greater than 4 months and 15 days.

To find the difference in months I am using =month(a2)-month(a1). Does anyone know a way to find the difference in months and days?

 

[xlbo]

Define "Month" for starters

For the purposes of checking when something should be done, the phrase 4 months and 15 days is not clear - 4 31 day months or 4 30 day months or a mixture of both......

Have a look in the FAQs section as there are at least 2 FAQs in there that will probably give you all you need and more

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[fkegley]

What I have always done is use a formula to compute the difference in months of the two dates (as you are already doing) and another formula to compute the difference in days of the two dates. I then concatenate the results with another formula to read something like " The difference is " & A10 & " months, " & B10 & " days", where A10 contains the number of months and B10 contains the number of days.

Frank kegley
fkegley@hotmail.com

 

[macropod]

Here's a macro to do the calculation, but the logic for a formula would be the same, and for your example you'd either multiply the years result by 12 and add that to the moths, or skip the year calc and not MOD the months. The macro works in both Word and Excel, and probably in other Office apps too.

Cheers

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