Access Excel VBA day month year difference between two dates

This Access Excel tutorial explains how to find complete year difference, month difference and date difference between two dates

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

difference_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

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *