Sabrina Ayon
In Excel, we utilize numerous functions to do the work speedily and efficiently. Excel VBA also has some built-in functions. The VBA DateDiff function is one of the built-in functions in excel. It’s categorized under the Date/Time functions in Excel VBA. In this article, we will take an overview of the Excel VBA DateDiff Function usage with an abundance of examples.
Download Practice Workbook
You can download the workbook and practice with them.
VBA DateDiff Function: Syntax & Arguments
The excel DateDiff function is one of the most basic functions used by VBA.
➧ Syntax
The syntax for the VBA DateDiff function is
DateDiff(interval,date1,date2,[firstdayofweek,[firstweekofyear]])
➧ Arguments
| Argument | Required/Optional | Explanation |
|---|---|---|
| intervals | Required | This is a String Expression exploit to find differences between two dates. The intervals have some settings. |
| “yyyy” – Years | ||
| “y” – Day of Year | ||
| “q” – Quarter | ||
| “m” – Month | ||
| “d” – Day | ||
| “w” – Weekday | ||
| “ww” – Week | ||
| “h” – Hour | ||
| “n” – Minute | ||
| “s” – Second | ||
| date1, date2 | Required | Date variant, those two dates are utilized in the calculation. |
| firstdayofweek | Optional | A constant that defines the first day of the week or Sunday by default. |
| firstweekofyear | Optional | A constant that defines the first week of the year or January 1 by default. |
➧ Return Value
A numeric value.
➧ Type
VBA Function (Date / Time Functions).
➧ Applies to
Excel office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011, Excel 2010, Excel 2007, Excel 2003
9 Different Examples of Using VBA DateDiff Function
The DateDiff function is a date/ time function categorized under VBA in excel, which calculates the difference between two dates. The function returns the long data value of intervals between two dates. There are a variety of intervals like days, months, years, etc indicated by the users. Let’s demonstrate some examples to understand how this function actually works.
1. VBA DateDiff Function to Find Difference between Dates
With the VBA DateDiff function, we can find the number of days between two dates. Let’s see the steps below.
STEPS:
- Firstly, go to the Developer tab on the ribbon.
- Secondly, click on Visual Basic or press Alt + F11 to open the Visual Basic Editor.
- Next, go to Insert and select Module from the drop-down menu.
- Another way to open the Visual Basic Editor is, simply right-click on the sheet and select View Code.
- In the VBA code, assume that we need to know the number of days between “15-01-2022” to “15-02-2022”. So, we take date1 as d1 (“15-01-2022”) and date2 as d2 (“15-02-2022”). As we already know, the DateDiff function returns a long data value which is why we take the long value as nDays. As we are going to calculate the days, we indicate the interval “D” which represents the days.
- Now, write down the VBA Code below.
VBA Code:
Sub DateDiff_func_Dates()
Dim d1 As Date
Dim d2 As Date
Dim nDays As Long
d1 = "15-01-2022"
d2 = "15-02-2022"
nDays = DateDiff("D", d1, d2)
MsgBox "Difference between two Dates: " & nDays
End Sub- Now, run the code by pressing F5 or clicking the Run Sub button.
Output:
As a result, it will show 31. The difference between two dates is 31.
Read More: Excel Date Picker for Entire Column
2. Detect Contrast between Months with VBA DateDiff Function
The VBA DateDiff function also helps us to get the number of months between two dates. To do this, go along with the steps down.
STEPS:
- In the beginning, go to the Developer tab > Visual Basic > Insert > Module.
- Or, right-click on the worksheet. Then, go to the View Code.
- Alternatively, pressing the keyboard shortcut Alt + F11 will also do the same.
- For example, suppose we want to see the number of months between two dates. The first date we take as d1 is “12-11-2021” and the second date is “15-01-2022” define as d2. Now, we want to perceive how many months are in between those two dates. While finding the months we specify the interval as “M” which constitutes the months.
- Now, write down the VBA Code to calculate the number of months between two dates.
VBA Code:
Sub DateDiff_func_Months()
Dim d1 As Date
Dim d2 As Date
Dim nMonths As Long
d1 = "12-11-2021"
d2 = "15-01-2022"
nMonths = DateDiff("M", d1, d2)
MsgBox "Difference between Months: " & nMonths
End Sub- Run the code by pressing F5 or clicking the Run Sub button.
Output:
Finally, the result is in the MsgBox now.
Read More: How to Use VBA FileDateTime Function in Excel (3 Uses)
3. Observe Variation among Years Using VBA DateDiff Function
If there is any need to find out the number of years among two dates, we can easily do that by using the VBA DateDiff function.
STEPS:
- At first, by the same token, right-click on the sheet, then select View Code.
- On the other hand, use the keyboard shortcut Alt + F11 to open the Visual Basic Editor.
- Here, consider that we have two dates d1 (“15-01-2018“) and d2 (“15-01-2022“). Now we wish to calculate the number of years in between them.
- For that, copy and paste the VBA code below.
VBA Code:
Sub DateDiff_func_Years()
Dim d1 As Date
Dim d2 As Date
Dim nYears As Long
d1 = "15-01-2018"
d2 = "15-01-2022"
nYears = DateDiff("YYYY", d1, d2)
MsgBox "Difference between Years: " & nYears
End Sub- After that, again press the F5 key or click on the Run Sub button to run the code as above examples.
Output:
Read More: How to Use IsDate Function in VBA (3 Examples)
4. DateDiff Function to Find Difference between Hours in Excel VBA
In this example, the VBA DateDiff function will calculate the number of hours between 9:30:00 AM to 9:30:00 PM on 2/10/2022 and will show the result 12.
STEPS:
- In the first place, right-click on the sheet then select View Code.
- In the second place, write down the code below.
VBA Code:
Sub DateDiff_func_Hours()
Dim d1 As Date
Dim d2 As Date
Dim nHours As Long
d1 = #2/10/2022 9:30:00 AM#
d2 = #2/10/2022 9:30:00 PM#
nHours = DateDiff("H", d1, d2)
MsgBox "Difference between Hours: " & nHours
End Sub- Then, run the code by clicking on the Run Sub button or using the keyboard shortcut F5 key.
Output:
And, finally, the result is in the MsgBox window.
Read More: VBA Date Function (12 Uses of Macros with Examples)
5. Get Weeks Discrepancy with VBA DateDiff Function
The calculation of the number of weeks is not that easy. We need to see the calendar and count the weeks manually. But with excel VBA DateDiff function, it’s really obvious to find the weeks in between two dates. Suppose, we want to calculate the weeks between the dates “1/1/2022” as d1 and “2/10/2022” as d2.
STEPS:
- First, open the visual basic editor by right-clicking on the sheet from the sheet bar and then going to View Code.
- Next, write down the VBA code here.
VBA Code:
Sub DateDiff_func_Weeks()
Dim d1 As Date
Dim d2 As Date
Dim nWeeks As Long
d1 = #1/1/2022#
d2 = #2/10/2022#
nWeeks = DateDiff("w", d1, d2)
MsgBox "Difference between Weeks: " & nWeeks
End Sub- Finally, Run the code or press the keyboard shortcut F5 key to run the code.
Output:
Finally, we can view the result.
Read More: How to Use the VBA Weekday Function (2 Suitable Examples)
Similar Readings:
- How to Use VBA Abs Function in Excel (9 Examples)
- Use Log Function in Excel VBA (5 Suitable Examples)
- How to Use VBA LTrim Function in Excel (4 Examples)
- Use the VBA Chr Function (2 Examples)
- How to Return a Value in VBA Function (Both Array and Non-Array Values)
6. Notice Difference at Intervals in Minutes in Excel
Sometimes, for many purposes, it’s necessary to calculate the minutes. We can do the calculation effortlessly with the help of the DateDiff function.
STEPS:
- For this, first, right-click on the worksheet. Then, go to View Code.
- Next, write down the VBA code below.
VBA Code:
Sub DateDiff_func_Minutes()
Dim d1 As Date
Dim d2 As Date
Dim nMinutes As Long
d1 = #2/10/2022 9:30:00 AM#
d2 = #2/10/2022 9:35:00 AM#
nMinutes = DateDiff("N", d1, d2)
MsgBox "Difference between Minutes: " & nMinutes
End Sub- After that, run the code by pressing the F5 key or clicking on the Run Sub button on the Visual Basic Editor.
Output:
And, that’s it.
Read More: How to Use VBA TimeValue Function (6 Relevant Examples)
7. Acquire Variation in Seconds by VBA DateDiff function
To calculate the seconds in between two intervals we may use the DateDiff function in excel VBA.
STEPS:
- First, In the same way as the above examples, simply right-click on the sheet and select View Code.
- Now, in the same way as above examples, write down the VBA Code below to find the count of the seconds in between two times of a given date 2/10/2022.
VBA Code:
Sub DateDiff_func_Seconds()
Dim d1 As Date
Dim d2 As Date
Dim nSeconds As Long
d1 = #2/10/2022 9:30:00 AM#
d2 = #2/10/2022 9:31:00 AM#
nSeconds = DateDiff("S", d1, d2)
MsgBox "Difference between Seconds: " & nSeconds
End Sub- Now, run the code by pressing F5 or clicking the Run Sub button.
Output:
As a result, it will show 60 in the MsgBox.
Read More: How to Use VBA TimeSerial in Excel (3 Examples)
8. Find Intervals in Sheets using VBA DateDiff Function
Now, in this example, we will look at how we can calculate the days, months, and years among the given dates. For this, we are going to use the dataset below which contains some start dates in column B and end dates in column C. We will calculate the number of days, months, and years sequentially in columns D, E, and F.
STEPS:
- First, we will do that by using a VBA code. For this, in the first place, right-click on the sheet, then select View Code.
- This will appear in the Visual Basic Editor.
- Next, copy and paste the VBA code below.
VBA Code:
Sub DateDiff_func()
Dim i As Long
For i = 5 To 8
Cells(i, 4).Value = DateDiff("D", Cells(i, 2), Cells(i, 3))
Next i
For j = 5 To 8
Cells(j, 5).Value = DateDiff("M", Cells(j, 2), Cells(j, 3))
Next j
For k = 5 To 8
Cells(k, 6).Value = DateDiff("YYYY", Cells(k, 2), Cells(k, 3))
Next k
End Sub- Now, press the F5 key or click on the Run Sub button to run the code.
Output:
Finally, we are able to see the result in column D, E, and F .
Related Content: How to Use Fix Function in Excel VBA (4 Examples)
9. Get the Number of Days between Given and Current Dates
We can calculate the number of days in a given date from today using this VBA Date function.
If we enter a date, it will return the difference of the number of days from today. To take today’s date we will use the Now function in our code.
STEPS:
- Correspondingly in the earlier examples, right-click on the sheet then select View Code.
- Now, likewise the previous example, write down the code below.
VBA Code:
Sub DateDiff_func_Today()
Dim d As Date
Dim Result
d = InputBox("Enter a Date")
Result = DateDiff("d", Now, d)
MsgBox "Days from today: " & Result
End Sub- Further, run the code by clicking on the Run Sub button or using the keyboard shortcut F5 key.
- And, this will open an input window, where we input the date from which we calculate the count of the number of days from today.
Output:
As a result, it has a minus (“–”) sign. It’s because the entered date is an earlier day and the difference is 65.
Read More: How to Use VBA WeekdayName Function in Excel (2 Examples)
Conclusion
The above examples are an overview of the VBA DateDiff function in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!
