This Access Excel tutorial explains how to find complete year difference, month difference and date difference between two dates.
You may also want to read:
Access Excel VBA difference between two dates (year difference, month difference, day difference)
In Excel worksheet, there is a Function called DateDif that can calculate difference between two dates in complete year, month or day.
In VBA, there is no DateDif Function. There is a similar Function, DateDiff, which is used to calculate date difference but it does not consider the whole date in the calculation. For example, if you want to find the year difference, the Function extracts only the year to calculate difference between two years, ignoring month and day.
Fortunately, you can still access DateDif in Excel VBA through Application.Worksheetfunction Property
The purpose for this topic is that:
1) Recreate DateDif Function for Access, Access VBA, Excel VBA
2) Count the end date in the calculation of difference
VBA Code – difference between two dates
Public Function wDateDif(stDt, edDt, interval) tempdt = stDt Select Case interval Case "Y" Do While edDt >= DateAdd("yyyy", 1, tempdt) - 1 tempdt = DateAdd("yyyy", 1, tempdt) counter = counter + 1 Loop Case "M" Do While edDt >= DateAdd("m", 1, tempdt) - 1 tempdt = DateAdd("m", 1, tempdt) counter = counter + 1 Loop Case "D" counter = edDt - stDt + 1 End Select wDateDif = counter End Function
Syntax – difference between two dates
wDateDif(stDt, edDt, interval)
|interval||“Y” – complete year difference
“M” – complete month difference
“D” – complete year difference
Example- difference between two dates
Formula of C2: =wdatedif(A2,B2,”Y”)
Formula of D2: =wdatedif(A2,B2,”M”)
Formula of E2: =wdatedif(A2,B2,”D”)