Calculating Date Differences Using VBA

by
datesfunctionsvba

In VBA there is a custom DateDiff function that allows you to easily calculate the difference between dates – whether you want the difference to be in days, weeks, months, years, it is easier to accomplish this in VBA than through regular Excel formulas. I have piggy-backed off the DateDiff function to make a custom function in Excel that makes it easy to use as a formula in your spreadsheet.

The custom function I have created is called datecalculation and consists of three arguments: start date, end date, and interval. The interval determines how the difference is calculated. The interval needs to be in quotations and use one of the following codes:

date difference intervals

Below is an example of how the function work when computing the difference between January 1, 2016 (cell C2) and January 1, 2017 (cell C3). The interval codes are in column A.The result column is the date difference according to the selected interval.

vba date difference function

To make this function work in your spreadsheet simply insert the following VBA code. If you are not sure how to do that, please refer to this post, specifically the section about inserting VBA code.
————————————————————————————————————————
Function datecalculation(date1 As Date, date2 As Date, interval As String)
datecalculation = DateDiff(interval, date1, date2)
End Function
————————————————————————————————————————

Add a Comment

You must be logged in to post a comment