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:

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.

Related Content: How to Count Date Occurrences in Excel


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.

Apply DateDiff Directly Using Dates in VBA Code

Step 2:

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

Apply DateDiff Directly Using Dates in VBA Code

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

Apply DateDiff Directly Using Dates in VBA Code

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

Apply DateDiff Directly Using Dates in VBA Code

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.


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

Use Cell Reference to Apply DateDiff Function in Excel VBA

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

Use Cell Reference to Apply DateDiff Function in Excel VBA

Step 5:

  • Again, press F5 to run this code.

Use Cell Reference to Apply DateDiff Function in Excel VBA

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

Related Content: How to Use COUNTIFS with Date Range in Excel (6 Easy Ways)


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

Take User Input to Perform the DateDiff Function

Step 3:

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

Take User Input to Perform the DateDiff Function

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.


Similar Readings:


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

Build a Custom Excel Function Using DateDiff

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)

Build a Custom Excel Function Using DateDiff

Step 5:

  • Now, press Enter.

Step 6:

  • Drag the Fill Handle icon towards the last cell.

Build a Custom Excel Function Using DateDiff

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


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

Different Format Result Using the DateDiff Function

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

Different Format Result Using the DateDiff Function

Month Format:

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

Different Format Result Using the DateDiff Function

Hour Format:

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

Different Format Result Using the DateDiff Function

Minute Format:

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

Different Format Result Using the DateDiff Function

Second Format:

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

Different Format Result Using the DateDiff Function

Week Format:

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

Different Format Result Using the DateDiff Function

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

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

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo