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.

**Table of Contents**hide

**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
```

- R
**un**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. 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

**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

**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

**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

**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

**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

**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

**Download Practice Workbook**

You can download the workbook and practice with them.

**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.