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:
Excel DATEDIF Function to calculate date difference
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
Application.Worksheetfunction.DateDif(st_date, ed_date)
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)
| stDt | Begin date |
| edDt | End Date |
| 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”)
Outbound References
http://www.techonthenet.com/excel/formulas/datedif.php