Months and Days Between Dates

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
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?
 

Some videos you may like

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
Joined
Jun 14, 2015
Messages
258
Office Version
  1. 2013
Platform
  1. Windows
Try
Code:
=DATEDIF(A2,B2,"y")&" Years, "&DATEDIF(A2,B2,"ym")&" Months, "&DATEDIF(A2,B2,"md")&" Days"
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Dec 15, 2005
Messages
1,213
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
Joined
Dec 15, 2005
Messages
1,213

ADVERTISEMENT

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
Joined
Dec 15, 2005
Messages
1,213
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
Joined
Jun 14, 2015
Messages
258
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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"
 

navic

Active Member
Joined
Jun 14, 2015
Messages
258
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
@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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,132
Messages
5,527,029
Members
409,737
Latest member
shanghity

This Week's Hot Topics

Top