Find the difference between two dates is simple if you're simply looking for the number of days between them. However, if you're looking to express the difference in a bit more user-friendly way, you can use the following code to calculate the difference in years, months, and days. The code accounts for zero values, plurals and the Oxford comma. Further, it automatically sets the later of the two dates to the End date, so the dates can be used interchangeably.
Examples:
June 4, 2010, July 3, 2012 returns "2 Years and 29 Days"
June 4, 2010, July 5, 2011 returns "1 Year, 1 Month and 1 Day"
June 4, 2010, June 5, 2010 returns "1 Day"
Examples:
June 4, 2010, July 3, 2012 returns "2 Years and 29 Days"
June 4, 2010, July 5, 2011 returns "1 Year, 1 Month and 1 Day"
June 4, 2010, June 5, 2010 returns "1 Day"
Code:
Function DiffOfTwoDates(dtmDate1 As Date, dtmDate2 As Date) As String
' Written by Will Knapp, Freelance Access Developer, 2013
Dim dtmStart As Date, dtmEnd As Date
Dim strDiff As String ' Resulting String
Dim yDiff As Integer ' Year Difference
Dim mDiff As Integer ' Month Difference
Dim dDiff As Integer ' Day Difference
Dim CommaLoc As Integer
' Assign the start and end dates accordingly
If dtmDate1 > dtmDate2 Then
dtmStart = dtmDate2
dtmEnd = dtmDate1
Else
dtmStart = dtmDate1
dtmEnd = dtmDate2
End If
If dtmStart = dtmEnd Then
strDiff = "0 Days"
' If the day number of the end date is equal to or larger than the day number of start month
ElseIf DatePart("d", dtmEnd) >= DatePart("d", dtmStart) Then
' find the month difference
mDiff = DateDiff("m", dtmStart, dtmEnd)
' Find the Day difference
dDiff = DatePart("d", dtmEnd) - DatePart("d", dtmStart)
' If the day number of the end date is less than the day number of the start month
Else
' Calculate the difference between the months and subtract one.
mDiff = DateDiff("m", dtmStart, dtmEnd) - 1
' Subtract the day number of the start month from the total days in that month
dDiff = DatePart("d", DateSerial(year(dtmStart), Month(dtmStart) + 1, 0)) - DatePart("d", dtmStart)
' Add the number of days in the end month
dDiff = dDiff + DatePart("d", dtmEnd)
End If
' Determine the number of years based on the number of months
yDiff = Int(mDiff / 12)
' Determine the number of extra months in addition to the years
mDiff = mDiff Mod 12
' Construct the string, accounting for plurals, ignoring and "zero" values
If dDiff > 0 Then strDiff = dDiff & IIf(dDiff = 1, " Day", " Days")
If mDiff > 0 Then strDiff = mDiff & IIf(mDiff = 1, " Month", " Months") & ", " & strDiff
If yDiff > 0 Then strDiff = yDiff & IIf(yDiff = 1, " Year", " Years") & ", " & strDiff
' Remove trailing space and comma that will be there if dDiff = 0
strDiff = Trim(strDiff)
If Right(strDiff, 1) = "," Then strDiff = Left(strDiff, Len(strDiff) - 1)
' Replace the Oxford comma with "and", if necessary
CommaLoc = InStrRev(strDiff, ",")
If CommaLoc > 0 Then strDiff = Left(strDiff, CommaLoc - 1) & " and" & Right(strDiff, Len(strDiff) - CommaLoc)
DiffOfTwoDates = strDiff
End Function
Last edited: