How to Use DateDiff Function in Excel VBA (5 Examples)

Microsoft Excel consists of lots of inbuilt functions. This DateDiff function is one of them. This DateDiff function is used in VBA and categorized as time or date function in VBA. This function returns the difference between two dates in VBA. In this article, we will discuss the use of the DateDiff function in Excel VBA.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Introduction to DateDiff Function

Function Objective:

This DateDiff function returns the time intervals between two specified dates. This time interval may be in any format like day, month, quarter, year, etc.

Syntax:

=DateDiff(interval, date1, date2, [ firstdayofweek, [ firstweekofyear ]] )

Argument:

Settings:

Here are the settings for different arguments.

The interval argument consists of the below settings

The firstdayofweek argument has the following settings:

The firstweekofyear argument has the following settings:

Returns:

The return will be in a time format specified by the user. But it will be an integer value.

5 Examples of Using DateDiff in Excel VBA

Here we will show different methods to use the DateDiff function in Excel VBA. We take a data set to show how to apply the DateDiff function.

1. Apply DateDiff Directly Using Dates in VBA Code

In this section, we will use the dates directly on the VBA code.

Step 1:

• First, go to the Developer tab.
• Then click on Record Macro.

Step 2:

• We will get a new dialog box.
• Here, we will set DateDiff1 as the Macro name.
• Then press OK.

Step 3:

• Now, put the below code in the VBA command module.

Sub DateDiff1()
Dim Msg
Msg = "Day Difference: " & DateDiff("d", "1/1/2022", "1/10/2022"
MsgBox Msg
End Sub

Step 4:

• Now, press F5 to run the code..

Here, we get the difference between the two dates. We used the day format and set the dates in the code directly.

We can also insert the DateValue function in this VBA code.  Modify the code by inserting the DateValue function and applying the below code.

Step 5:

• We modified the code by inserting the DateValue function and applying the below code.

Sub DateDiff1()
Dim Msg
Msg = "Day Difference: " & DateDiff("d", DateValue("January 3, 2019"), DateValue("January 5, 2022"))
MsgBox Msg
End Sub

Step 6:

• Again, press F5 to run the code.

So, we can see that the DateValue function is also applicable here.

The DateSerial function also can be used here.

Step 7:

• Here, we will insert the DateSerial function in the code and modify the code accordingly.
• Now, apply the below-modified code.

Sub DateDiff1()
Dim Msg
Msg = "Day Difference: " & DateDiff("d", DateSerial(2007, 3, 3), DateSerial(2020, 12, 5))
MsgBox Msg
End Sub

Step 8:

• Again, press F5 to run the code.

So, we get the difference between two dates by directly inputting dates in the code in multiple ways.

Read More: How to Subtract Dates in Excel to Get Years (7 Simple Methods)

2. Use Cell Reference to Apply DateDiff Function in Excel VBA

In this section, we will use two cell references in the VBA code to apply the DateDiff function.

Step 1:

• We will create a new macro named DateDiff2 for this section.

Step 2:

• Now, apply the below code in the command module.

Sub DateDiff2()
Dim Msg
Msg = "Day Difference: " & DateDiff("d", Range("B5").Value, Range("C5").Value)
MsgBox Msg
End Sub

Step 3:

• Now, press F5 to run the code.

Here, we used cell references to find the difference between the two dates.

Step 4:

• Now change the cell references and apply the below code.

Sub DateDiff2()
Dim Msg
Msg = "Day Difference: " & DateDiff("d", Range("B6").Value, Range("C6").Value)
MsgBox Msg
End Sub

Step 5:

• Again, press F5 to run this code.

As the cell reference changes, dates also change. And we get the changed result then.

Read More: Calculate Years and Months between Two Dates in Excel (6 Approaches)

3. Take User Input to Perform the DateDiff Function

Here, we will perform the DateDiff function by taking user input.

Step 1:

• Create a new macro named DataDiff3 for this method.

Step 2:

• Put the below code in the command module.
• Then press F5 to run the code.

Sub DateDiff3()
Dim DateStart As Date
Dim DateEnd As Date
Dim Msg
DateStart = InputBox("Enter start date")
DateEnd = InputBox("Enter end date")
Msg = "Day Difference: " & DateDiff("d", DateStart, DateEnd)
MsgBox Msg
End Sub

Step 3:

• A new window will appear to put the start date.
• Put the desired date and click on OK.

Step 4:

• Now, another window appears to input the end date.
• Put the end date and press OK.

Step 5:

• Now, we get the difference between the given dates.

By applying this method, we input any date as our requirement.

Read More: How to Count Days from Date to Today Automatically Using Excel Formula

Similar Readings

• Add Weeks to a Date in Excel (4 Simple Methods)
• How to Add Years to a Date in Excel (3 Easy Ways)
• Calculate Number of Months Between Two Dates in Excel
• How to Add Days to a Date in Excel Excluding Weekends (4 Ways)

4. Build a Custom Excel Function Using DateDiff

In this section, we will build a custom Excel function using the DateDiff function in VBA.

Step 1:

• Create a new macro named DateDiff4.

Step 2:

• Now, write the below code on the command module.
• Then save this code.

Function TestDates(pDate1 As Date, pDate2 As Date) As Long
TestDates = DateDiff("d", pDate1, pDate2)
End Function

Step 3:

• Now, go to the data set.
• Add a column named Result.

Step 4:

• Go to Cell D5.
• Write the below formula:

Step 5:

• Now, press Enter.

Step 6:

• Drag the Fill Handle icon towards the last cell.

This is how we build a custom function using the DateDiff function in VBA.

Read More: How to Calculate Number of Days Between Two Dates in Excel

5. Different Format Result Using the DateDiff Function

We can get the result of two dates in different time formats. Previously we showed only day values. We will show other formats also here.

Step 1:

• We add a macro named DateDiff5 for this section.

Step 2:

• Now apply the below code in the command module of VBA.

Sub DateDiff5()
Dim Msg
Msg = "Year Difference: " & DateDiff("yyyy", "3/3/2007", "5/12/2020")
MsgBox Msg
End Sub

Step 3:

• Press F5 to run the code.

Here, we get the result in the year format. We can get results in other formats only by changing the first argument of the function.

Corresponding VBA codes for different time formats are given below:

Quarter Format:

Sub DateDiff5()
Dim Msg
Msg = "Quarter Difference: " & DateDiff("q", "3/3/2007", "5/12/2020")
MsgBox Msg
End Sub

Month Format:

Sub DateDiff5()
Dim Msg
Msg = "Difference: " & DateDiff("m", "3/3/2007", "5/12/2020")
MsgBox Msg
End Sub

Hour Format:

Sub DateDiff5()
Dim Msg
Msg = "Hour Difference: " & DateDiff("h", "3/3/2007", "5/12/2020")
MsgBox Msg
End Sub

Minute Format:

Sub DateDiff5()
Dim Msg
Msg = "Minute Difference: " & DateDiff("n", "3/3/2007", "5/12/2020")
MsgBox Msg
End Sub

Second Format:

Sub DateDiff5()
Dim Msg
Msg = "Second Difference: " & DateDiff("s", "3/3/2007", "5/12/2020")
MsgBox Msg
End Sub

Week Format:

Sub DateDiff5()
Dim Msg
Msg = "Week Difference: " & DateDiff("w", "3/3/2007", "5/12/2020")
MsgBox Msg
End Sub

In this way, we can get the result in a different time format.

Read More: How to Count Months from Date to Today by Using Excel Formula

Things to Remember

The date formatting must match with the calendar property. Suppose the Calendar property setting is Gregorian, the supplied date must be Gregorian.

Conclusion

In this article, we described the use of the DateDiff function in Excel VBA. I hope this will satisfy your needs. Please have a look at our website ExcelDemy.com and give your suggestions in the comment box.

Related Articles

• How to Calculate Working Days Excluding Sundays in Excel
• [Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel
• How to Calculate Working Days in a Month in Excel 
• Find Number of Weeks Between Two Dates in Excel
• How to Insert Dates in Excel Automatically