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:
ARGUMENT | REQUIRED/OPTIONAL | EXPLANATION |
---|---|---|
interval | REQUIRED | This argument indicates in which form differences will appear. |
date1 | REQUIRED | Starting date for use in the function. |
date2 | REQUIRED | End date used in the function. |
firstdayofweek | OPTIONAL | This sets the starting day of the week. |
Settings:
Here are the settings for different arguments.
The interval argument consists of the below settings
Setting | Description |
---|---|
yyyy | Year |
y | Day of year |
q | Quarter |
m | Month |
d | Day |
w | Weekday |
ww | Week |
h | Hour |
n | Minute |
s | Second |
The firstdayofweek argument has the following settings:
Constant | Value | Description |
---|---|---|
vbUseSystem | 0 | Use the NLS API setting. |
vbSunday | 1 | Sunday |
vbMonday | 2 | Monday |
vbTuesday | 3 | Tuesday |
vbWednesday | 4 | Wednesday |
vbThursday | 5 | Thursday |
vbFriday | 6 | Friday |
vbSaturday | 7 | Saturday |
The firstweekofyear argument has the following settings:
Constant | Value | Description |
---|---|---|
vbUseSystem | 0 | Use the NLS API setting. |
vbFirstJan1 | 1 | The week of January 1 is considered the starting week. |
vbFirstFourDays | 2 | The week which has at least four days in the new year is considered as the first week. |
vbFirstFullWeek | 3 | Start with the first full week of the year. |
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:
=Difference(B5,C5)
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.