CODEWITHSUNDEEP
vbaexcel
deeplay
deeplay

Reputation: 386

Exact Date difference in months and days

DateDiff calculates incorrect:

DateDiff("m", "30/06/2011", "24/06/2012") will return 12

But i need to return 11,because real difference is 11 months and 25 days

Maybe anyone have specific solution for this question

That will be ideal for me: 11 months and 25 days

Upvotes: 2

Views: 4788

Answers (2)

Tom Collins
Tom Collins

Reputation: 4069

Here's a cleaner solution than the above custom made function. Mostly it uses the built-in DateDiff function, but then adjusts the answer if it rounds up. My function will also give you the difference even if the the first date is later than the second and optionally add some text saying so. 

Function YearsMonthsDays(Date1 As Date, _
                     Date2 As Date, _
                     Optional ShowAll As Boolean = False, _
                     Optional Grammar As Boolean = True, _
                     Optional MinusText As String = "Minus " _
                     ) As String
Dim dTempDate As Date
Dim iYears As Integer
Dim iMonths As Integer
Dim iDays As Integer
Dim sYears As String
Dim sMonths As String
Dim sDays As String
Dim sGrammar(-1 To 0) As String
Dim sMinusText As String

If Grammar = True Then
    sGrammar(0) = "s"
End If


If Date1 > Date2 Then
    dTempDate = Date1
    Date1 = Date2
    Date2 = dTempDate
    sMinusText = MinusText
End If

iYears = DateDiff("yyyy", Date1, Date2)
Date1 = DateAdd("yyyy", iYears, Date1)
If Date1 > Date2 Then
    iYears = iYears - 1
    Date1 = DateAdd("yyyy", -1, Date1)
End If

iMonths = DateDiff("M", Date1, Date2)
Date1 = DateAdd("M", iMonths, Date1)
If Date1 > Date2 Then
    iMonths = iMonths - 1
    Date1 = DateAdd("m", -1, Date1)
End If

iDays = DateDiff("d", Date1, Date2)

If ShowAll Or iYears > 0 Then
    sYears = iYears & " year" & sGrammar((iYears = 1)) & ", "
End If
If ShowAll Or iYears > 0 Or iMonths > 0 Then
    sMonths = iMonths & " month" & sGrammar((iMonths = 1)) & ", "
End If
sDays = iDays & " day" & sGrammar((iDays = 1))

YearsMonthsDays = sMinusText & sYears & sMonths & sDays
End Function

Upvotes: 3

Leniel Maccaferri
Leniel Maccaferri

Reputation: 102428

You won't get it with pure DateDiff. If you use this custom made function, you get the result the way you want like: 

0 years, 1 months, 1 days

You can also take a look at the DateDif function with a (single f in the end). Be aware that DateDif() still gives some results that may be unexpected (i.e, negative number of days) when the ending month has fewer days than the starting month.

Upvotes: 0

Related Questions