VBA DateDiff Function

What is the DateDiff Function in Excel VBA?

DateDiff is a time intelligence function available as a VBA function and categorized as a date and time function. The VBA DateDiff function can determine the difference between two dates. The specialty of this function is that we can specify the difference required between the two dates, i.e., we can get the difference in months, years, days, quarters, etc. We can even get the difference in hours, minutes if the two given dates have the time stamps.

For example, look at the following data in Excel. We have two dates in cells A2 and B2 respectively and we need to find the difference between these two dates in days.

The following VBA code with VBA DateDiff function will get the difference between these two dates in cell C2. Once we run this VBA code, we will get the following result in cell C2.

Sub DateDiff_Intro()
Range(“C2”).Value = DateDiff(“d”, Range(“B2”).Value, Range(“A2”).Value)
End Sub

Syntax

We can even get the difference in hours, minutes if the two given dates have the time stamps. To understand the VBA DateDiff function let’s have look at the syntax of the VBA DateDiff function.

DateDiff( Interval As String, Date1, Date2, [FirstDayOfWeek As VbDayofWeek = vbSunday, FirstWeekOfYear As VbFirstWeekOfYear = vbFirstJan1])

Interval: When we calculate the difference between two dates, what we wish to calculate matters, i.e., whether we need the difference in months, days, years, or quarters. We can provide this as a string in the interval argument of the VBA DateDiff function.

Following are the intervals we can give to get the desired difference.

Interval	Difference Unit
d	Days
m	Months
q	Quarters
yyyy	Years
w	Week Day
y	Day of Year
www	Week of Year
h	Hour
n	Minutes
s	Seconds

We can give any one of the above intervals in double quotes to get the desired difference.

• Date 1: The first date of the two dates.
• Date 2: The second date of the two dates.

The formula would read as follows.

Date 2 – Date 1

The rest of the arguments are optional.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to Use DateDiff Function in Excel VBA?

Let us show you an example of using the VBA DateDiff Function with a step-by-step approach. Assume we have two dates, i.e., 10-Jan-2023 and 15-Mar-2023, and we want to know the difference between these two dates in days. Let’s follow the steps listed below.

Step 1: Start the sub procedure by naming the macro in excel.

Step 2: Define a variable that can hold the result given by the VBA DateDiff function.

Since the result of the VBA DateDiff function is a number, we have given the VBA data type as “Long.”

Step 3: For the defined variable, let’s assign a value by entering the VBA DateDiff function.

Step 4: The first argument of the VBA DateDiff function is the interval. We need to find the difference between two dates in days; hence the interval will be “d.”

Step 5: For the Date1 argument, we must give the lowest date, i.e., 10-Jan-2023.

Step 6: For the Date2 argument, we must give the highest date, i.e., 15-Mar-2023.

Step 7: The rest of the arguments of the VBA DateDiff function are not required.

Step 8: The result of the DateDiff function is assigned to the variable “DateDiff_Result.” Let’s show the result in a message box.

Execute the code by pressing the F5 key. We should be able to see the following message box, which shows the difference between the two dates in days, that is 64.

Code:

Sub DateDiff_Basic()
Dim DateDIff_Result As Long
DateDIff_Result = DateDiff(“d”, “10-01-2023”, “15-03-2023”)
MsgBox DateDIff_Result
End Sub

Examples Let us show you different kinds of examples of applying the VBA DateDiff Function in different scenarios.

Example #1: To Find Difference in Days

Similarly, assume we have two dates, “05-02-2021” and “05-02-2023”. Let’s find the difference between these two dates in days first. The following code will give us the difference in days.

Code Explanation:

Part #1: We have defined two variables with Date data type to assign two dates.

Part #2: For the defined variables, we have assigned two respective dates.

Part #3: We have defined another variable with Long data type to get the value returned by the VBA DateDiff days function.

Part #4: For the variable defined in Part #3, we are applying the VBA DateDiff function.

Part #5: Now, we show the variable result in a message box.

Once we run the code, we will get the result in a message box.

Example #2: To Find Difference in Months

VBA DateDiff function is versatile because we can find the difference between two dates in multiple units. In the previous example, we have seen the difference in days; similarly, we can find the difference in months using the same dates.

All we must do is change the “interval” argument of the VBA DateDiff function. Look at the following code.

Sub DateDiff_Ex2()
‘Define variables to assig two dedicated dates
Dim Dt1 As Date
Dim Dt2 As Date
‘Assign dates to variables
Dt1 = “05-02-2021”
Dt2 = “05-02-2023”
‘Define a variable to showcase the result of the datediff function
Dim DD_Result As Long
‘Assign the DateDiff function result to variable. Difference in months hence “m” is the argument value
DD_Result = DateDiff(“m”, Dt1, Dt2)
‘Show the end result in a message box
MsgBox DD_Result
End Sub

As we can see, we have changed only the interval argument from “d” (days) to “m” (months). Let us run the code, and we will get the following result in a message box.

The difference between the two dates is 24 months; hence we see the value 24 in a message box. It is the VBA DateDiff months calculation.

Example #3: To Find Difference in Years

We can also get the difference between two dates in years; all we need to do is change the interval of the VBA DateDiff function.

Look at the following code.

Sub DateDiff_Ex3()
‘Define variables to assig two dedicated dates
Dim Dt1 As Date
Dim Dt2 As Date
‘Assign dates to variables
Dt1 = “05-02-2021”
Dt2 = “05-02-2023”
‘Define a variable to showcase the result of the datediff function
Dim DD_Result As Long
‘Assign the DateDiff function result to variable. Difference in months hence “m” is the argument value
DD_Result = DateDiff(“yyyy”, Dt1, Dt2)
‘Show the end result in a message box
MsgBox DD_Result
End Sub

We have just changed the interval of the VBA DateDiff function to “yyyy.” Let’s execute the code, and we will get the following result.

The difference in years is 2 years.

Example #4: DateDiff Function with Loop

So far, we have seen a single date used in a variable. However, in the real world, we need to deal with multiple dates; hence we need to use a loop to go through each cell. For example, look at the following data in Excel.

We have insurance policy data with each policy number’s commencement and expiration dates. We must find the policy duration in days, months, and years. Here we have data in more than one cell. Hence, we must use the FOR NEXT loop to go through each cell to apply the same logic. For example, look at the following code.

Sub DateDiff_Ex4()
‘Define variables to be used for FOR NEXT loop
Dim k As Long
Dim i As Long
‘Define a varaible to find the last used row
Dim LR As Long
‘Find the last used row
LR = Cells(Rows.Count, 1).End(xlUp).Row
‘Define a variable to find the datediff fuction interval
Dim Interval As String
‘Use loop to go through all the cells
‘We need to start the loop from column 4 to 6, hence the starting position of the loop will be 4
For k = 4 To 6
‘Find the interval unit for datediff function based on the current column
‘For example, if we are in the 4th column then we are finding the difference in days, hence interval will be “d”
If k = 4 Then
Interval = “d”
‘if we are in the 5th column then we are finding the difference in months, hence interval will be “m”
ElseIf k = 5 Then
Interval = “m”
Else
‘if we are in the 6th column then we are finding the difference in years, hence interval will be “yyyy”
Interval = “yyyy”
End If
‘Apply VBA DateDiff Function based on the current looping column
For i = 2 To LR
Cells(i, k).Value = DateDiff(Interval, Cells(i, 2).Value, Cells(i, 3).Value)
Next i
Next k
End Sub

Once we run the code, it will apply the VBA DateDiff function for the data and will give us the following result.

We have got the difference in days in column D, the difference in months in column E, and the difference in years in column F.

Assignment as a Practice

We have gone through various examples, and we hope that you have learnt about the VBA DateDiff function. Hence, we are giving you the following assignment as practice. Nothing complicated, all you need to do is use loops with some minor changes from our example #4.

If you need help building your code, here’s the code below.

Sub DateDiff_Assignment()
‘Define variables to be used for FOR NEXT loop
Dim k As Long
Dim i As Long
‘Define a varaible to find the last used row
Dim LR As Long
‘Find the last used row
LR = Cells(Rows.Count, 1).End(xlUp).Row
‘Define a variable to find the datediff fuction interval
Dim Interval As String
‘Use loop to go through all the cells
‘We need to start the loop from column 4 to 6, hence the starting position of the loop will be 4
For k = 3 To 5
‘Find the interval unit for datediff function based on the current column
‘For example, if we are in the 3rd column then we are finding the difference in days, hence interval will be “d”
If k = 3 Then
Interval = “d”
‘if we are in the 4th column then we are finding the difference in months, hence interval will be “m”
ElseIf k = 4 Then
Interval = “m”
Else
‘if we are in the 5th column then we are finding the difference in years, hence interval will be “yyyy”
Interval = “yyyy”
End If
‘Apply VBA DateDiff Function based on the current looping column
For i = 2 To LR
Cells(i, k).Value = DateDiff(Interval, Cells(i, 1).Value, Cells(i, 2).Value)
Next i
Next k
End Sub

You can use this code to develop your own code with little amendments.

Important Things to Note

• VBA DateDiff function accepts only date values; if any value other than date is provided, it will throw an error.
• If you find the difference in years, give the interval year (yyyy) four times; otherwise, it will throw an error. However, if you give only one “y,” then it will show the difference in days, not in years. If the given date is not of datatype Date or is the wrong date, then we get a Type Mismatch error in VBA.
• VBA DateDiff minutes and VBA DateDiff seconds calculation requires the time part in the date.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand VBA DateDiff Function formulas and examples. We can download the template here to use it instantly.

Recommended Articles

This has been a guide to What is VBA DateDiff Function. We learn how to use Datediff function to find the differences between two dates with examples. You can learn more from the following articles –

• VBA Selection
• VBA Screen Update
• VBA Date Function