Months and Days Between Dates

mgirvin

mgirvin

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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
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

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][td="bgcolor:#C0C0C0"]
D​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td="bgcolor:#F3F3F3"]
StartDate​
[/td][td="bgcolor:#F3F3F3"]
EndDate​
[/td][td="bgcolor:#F3F3F3"]
m.dd​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td]
3/28/2017​
[/td][td]
3/1/2018​
[/td][td="bgcolor:#CCFFCC"]
11.01​
[/td][td="bgcolor:#CCFFCC"]C2: =iDateDif(A2, B2, "m") + iDateDif(A2, B2, "md")/100[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td]
3/29/2017​
[/td][td]
3/1/2018​
[/td][td="bgcolor:#CCFFCC"]
11.01​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td]
3/30/2017​
[/td][td]
3/1/2018​
[/td][td="bgcolor:#CCFFCC"]
11.01​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td]
3/31/2017​
[/td][td]
3/1/2018​
[/td][td="bgcolor:#CCFFCC"]
11.01​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
6​
[/td][td]
1/2/2019​
[/td][td]
9/25/2019​
[/td][td="bgcolor:#CCFFCC"]
8.23​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
7​
[/td][td]
1/25/2019​
[/td][td]
4/6/2019​
[/td][td="bgcolor:#CCFFCC"]
2.12​
[/td][td][/td][/tr]
[/table]
 
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
mgirvin said:
The formula still yields negative days in certain situations.
Click to expand...
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
You must log in or register to reply here.

Similar threads

T
  • Question Question
Years, Months, Days - Years, Months, Days
Replies
3
Views
679
MARK858
MARK858
BradleyS
  • Solved
Convert Age to Number format not working
Replies
7
Views
109
BradleyS
BradleyS
SanjayGMusafir
  • Solved
Text function not returning correct month
Replies
7
Views
160
Alex Blakenburg
A
S
  • Question Question
Formula for Equity Vesting
Replies
6
Views
1K
Dave Patton
D
K
  • Question Question
VB to rename tabs based on cell information in first tab
Replies
4
Views
166
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

Share this page

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