Months and Days Between Dates

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,236
Office Version
  1. 365
Platform
  1. Windows
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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Try
Code:
=DATEDIF(A2,B2,"y")&" Years, "&DATEDIF(A2,B2,"ym")&" Months, "&DATEDIF(A2,B2,"md")&" Days"
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
Thanks, shg. Thanks for the VBA and assumptions. I am looking for a formula, but perhaps I can try your assumptions.
 
Upvote 0
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.
 
Upvote 0
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"
 
Upvote 0
@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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top