# Months and Days Between Dates

#### mgirvin

##### Well-known Member
Dear Team,

I do not accurately know how to calculate months and days between two dates. I am not even sure what sort of assumptions that I should make.

If I have these dates:

StartDate EndDate
3/28/2017 3/1/2018
3/29/2017 3/1/2018
3/30/2017 3/1/2018
3/31/2017 3/1/2018
1/2/2019 9/25/2019
1/25/2019 4/6/2019

a formula like this: =DATEDIF(A2,B2,"m")&" months & "&B2-EDATE(A2,C2)&" days" yields this: 11 months & 1 days
a formula like this: =DATEDIF(A3,B3,"m")&" months & "&B3-EDATE(A3,C3)&" days" yields this: 11 months & 1 days
a formula like this: =DATEDIF(A4,B4,"m")&" months & "&B4-EDATE(A4,C4)&" days" yields this: 11 months & 1 days
a formula like this: =DATEDIF(A5,B5,"m")&" months & "&B5-EDATE(A5,C5)&" days" yields this: 11 months & 1 days

a formula like this: =DATEDIF(A2,B2,"m")&" months & "&ABS(DATEDIF(A2,B2,"md"))&" days" yields this: 11 months & 1 days
a formula like this: =DATEDIF(A3,B3,"m")&" months & "&ABS(DATEDIF(A3,B3,"md"))&" days" yields this: 11 months & 0 days
a formula like this: =DATEDIF(A4,B4,"m")&" months & "&ABS(DATEDIF(A4,B4,"md"))&" days" yields this: 11 months & 1 days
a formula like this: =DATEDIF(A5,B5,"m")&" months & "&ABS(DATEDIF(A5,B5,"md"))&" days" yields this: 11 months & 2 days

a formula like this: =DATEDIF(A2,B2,"m")&" months & "&DATEDIF(A2,B2,"md")&" days" yields this: 11 months & 1 days
a formula like this: =DATEDIF(A3,B3,"m")&" months & "&DATEDIF(A3,B3,"md")&" days" yields this: 11 months & 0 days
a formula like this: =DATEDIF(A4,B4,"m")&" months & "&DATEDIF(A4,B4,"md")&" days" yields this: 11 months & -1 days
a formula like this: =DATEDIF(A5,B5,"m")&" months & "&DATEDIF(A5,B5,"md")&" days" yields this: 11 months & -2 days

What sort of formulas do people use in this situation and what are the assumptions? Any ideas?

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### navic

##### Active Member
Try
Code:
``=DATEDIF(A2,B2,"y")&" Years, "&DATEDIF(A2,B2,"ym")&" Months, "&DATEDIF(A2,B2,"md")&" Days"``

#### shg

##### MrExcel MVP
Here's what I use:

Code:
``````Function iDateDif(ByVal tBeg As Date, ByVal tEnd As Date, _
sInt As String) As Variant
' shg 2015

' VBA or UDF

' Replacement for the Excel DATEDIF Function with behavior
' complementary to the DateAdd function. The arguments are:

'   tBeg: Earlier of two dates

'   tEnd: Later of two dates

'     Time is stripped from both dates by truncation.

'   sInt: Interval, which may be one of:
'     y   number of full years
'     ym  residual number of full months after full years
'     yd  residual number of days after full years
'     m   number of full months
'     md  residual of days after full months
'     d   days

' Disparities between iDateDif and DATEDIF:
'                         ------- DATEDIF --------    ------- iDateDif -------
'    From        To       y  ym   yd   m  md    d     y  ym   yd   m  md    d
' 03/29/2012 03/11/2015   2  11  347  35  10* 1077    2  11  347  35  11* 1077
' 05/23/2012 05/22/2016   3  11  364* 47  29  1460    3  11  365* 47  29  1460

' *In the two cases of disagreement above, this function is correct.
' Discrepancies like the above are the reason for this function.

Dim nYr           As Long
Dim nMo           As Long

tBeg = Int(tBeg)
tEnd = Int(tEnd)

If tBeg > tEnd Then
iDateDif = CVErr(xlErrValue)

Else
Select Case LCase(sInt)
Case "y"
nYr = Year(tEnd) - Year(tBeg)
iDateDif = nYr + (DateAdd("yyyy", nYr, tBeg) > tEnd)

Case "ym"
nYr = iDateDif(tBeg, tEnd, "y")
iDateDif = iDateDif(DateAdd("yyyy", nYr, tBeg), tEnd, "m")

Case "yd"
nYr = iDateDif(tBeg, tEnd, "y")
iDateDif = iDateDif(DateAdd("yyyy", nYr, tBeg), tEnd, "d")

Case "m"
nYr = iDateDif(tBeg, tEnd, "y")
For nMo = 12 * nYr + 1 To 12 * nYr + 11
If DateAdd("m", nMo, tBeg) > tEnd Then Exit For
Next nMo
iDateDif = nMo - 1&

Case "md"
nMo = iDateDif(tBeg, tEnd, "m")
iDateDif = iDateDif(DateAdd("m", nMo, tBeg), tEnd, "d")

Case "d"
iDateDif = CLng(tEnd - tBeg)

Case Else
iDateDif = "Interval!"
End Select
End If
End Function``````

 A​ B​ C​ D​ 1​ StartDate​ EndDate​ m.dd​ ​ 2​ 3/28/2017​ 3/1/2018​ 11.01​ C2: =iDateDif(A2, B2, "m") + iDateDif(A2, B2, "md")/100 3​ 3/29/2017​ 3/1/2018​ 11.01​ 4​ 3/30/2017​ 3/1/2018​ 11.01​ 5​ 3/31/2017​ 3/1/2018​ 11.01​ 6​ 1/2/2019​ 9/25/2019​ 8.23​ 7​ 1/25/2019​ 4/6/2019​ 2.12​

Last edited:

#### mgirvin

##### Well-known Member
Thanks for the post, navic. That was not quite what I was looking for. The formula still yields negative days in certain situations. Unless... What are your assumptions that makes negative days useful or helpful?

#### mgirvin

##### Well-known Member

Thanks, shg. Thanks for the VBA and assumptions. I am looking for a formula, but perhaps I can try your assumptions.

#### mgirvin

##### Well-known Member
Shg, I got same result with iDate as with:

a formula like this: =DATEDIF(A2,B2,"m")&" months & "&B2-EDATE(A2,C2)&" days" yields this: 11 months & 1 days
a formula like this: =DATEDIF(A3,B3,"m")&" months & "&B3-EDATE(A3,C3)&" days" yields this: 11 months & 1 days
a formula like this: =DATEDIF(A4,B4,"m")&" months & "&B4-EDATE(A4,C4)&" days" yields this: 11 months & 1 days
a formula like this: =DATEDIF(A5,B5,"m")&" months & "&B5-EDATE(A5,C5)&" days" yields this: 11 months & 1 days

at least for the dates I tried.

#### navic

##### Active Member

The formula still yields negative days in certain situations.
Maybe this
Code:
``=DATEDIF(A2.B2."y")&" Years, "&IF(DATEDIF(A2.B2."md")<0.DATEDIF(A2.B2."ym")-1.DATEDIF(A2.B2."ym"))&" Months, "&IF(DATEDIF(A2.B2."md")>=0.DATEDIF(A2.B2."md").31+DATEDIF(A2.B2."md"))&" Days"``

#### shg

##### MrExcel MVP
Shg, I got same result with iDate as with: ...
That's similar to how the UDF works.

#### navic

##### Active Member
a formula like this: =DATEDIF(A2,B2,"m")&" months & "&ABS(DATEDIF(A2,B2,"md"))&" days" yields this: 11 months & 1 days
Any ideas?
I think this formula is OK

#### shg

##### MrExcel MVP
@mgirvin,

I have a routine I used to test the function that tests all intervals over four years. If you do VBA, you could easily adapt it to test your formula vs my function (or any formula or function vs any other formula or function). Happy to post if you're interested.

Replies
1
Views
44
Replies
7
Views
122
Replies
2
Views
278
Replies
1
Views
53
Replies
4
Views
57