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

Get FREE Advanced Excel Exercises with Solutions!

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.

VBA DateDiff Function to Find Difference in Date

  • Next, go to Insert and select Module from the drop-down menu.

VBA DateDiff Function to Find Difference in Date

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

VBA DateDiff Function to Find Difference in Date

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.

Detect Contrast between Months with VBA DateDiff Function

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

Detect Contrast between Months with VBA DateDiff Function

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.

Observe Variation among Years Using VBA DateDiff Function

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

DateDiff Function to Find Difference between Hours in Excel VBA

Output:

And, finally, the result is in the MsgBox window.

DateDiff Function to Find Difference between Hours in Excel VBA

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.

Get Weeks Discrepancy with VBA DateDiff Function

Output:

Finally, we can view the result.

Get Weeks Discrepancy with VBA DateDiff Function

Read More: How to Use the VBA Weekday Function (2 Suitable Examples)


Similar Readings:


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.

Acquire Variation in Seconds by VBA DateDiff function

Output:

As a result, it will show 60 in the MsgBox.

Acquire Variation in Seconds by VBA DateDiff function

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.

Find Intervals in Sheets using VBA DateDiff Function

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.

Find Intervals in Sheets using VBA DateDiff Function

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!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Team Leader. I'm a graduate in BSc in Computer Science and Engineering from United International University. I love working with computers and solving problems. I’ve always been interested in research and development. Here I post articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo