Excel VBA DateDiff Function

The DateDiff function in VBA returns a Long data value representing the number of intervals between two specified dates/times where the type of interval is supplied by the user.

Syntax

DateDiff (Interval, Date1, Date2, [FirstDayOfWeek], [FirstWeekOfYear])

Parameter

Interval (required) – This parameter represents a string specifying the interval to be used.

It can take the following values:

• “d” - Days
• “h” - Hours
• “n” - Minutes
• “m” - Months
• “q” - Quarters
• “s” - Seconds
• “ww” – Weeks
• “yyyy” - Years

Date1 (required) – This parameter represents a date value, specifying the start date/time for the calculation.

Date2 (required) – This parameter represents a date value, specifying the end date/time for the calculation.

FirstDayOfWeek (optional) – This parameter represents the weekday that should be used as the first day of the week.

It can take the following values:

• vbUseSystemDayOfWeek - The first day of the week is as specified in your system settings. The default value is set to vbSunday.
• vbSunday – Sunday
• vbMonday – Monday
• vbTuesday – Tuesday    
• vbWednesday – Wednesday
• vbThursday – Thursday
• vbFriday - Friday
• vbSaturday - Saturday

FirstWeekOfYear (optional) - This parameter represents the week that should be used as the first week of the year. The default value is set to vbFirstJan1.

It can take the following values:

• vbSystem – It signifies the first week of the year is as specified in your system settings
• vbFirstJan1 – It signifies the week in which Jan 1st occurs.
• vbFirstFourDays – It signifies the first week that contains at least four days in the new year.
• vbFirstFourDays – It signifies the first full week in the new year

Return

This function returns a Long data value representing the number of intervals between two specified dates/times.

Example 1

Sub DateDiffFunction_Example1()
 Dim datediff1 As Date
 Dim datediff2 As Date
 Dim nDays As Long
 datediff1 = #11/29/2019#
 datediff2 = #12/31/2020#
 nDays = datediff("d", datediff1, datediff2) 
 ' The variable nDays will return the value 398
 Cells(1, 1).Value = nDays
 End Sub 

Output

398

Example 2

Sub DateDiffFunction_Example2()
 Dim datediff1 As Date
 Dim datediff2 As Date
 Dim nWeek As Long
 datediff1 = #11/29/2019# 
 datediff2 = #10/31/2019#
 nWeek = datediff ("w", datediff1, datediff2)
 ' The variable nWeek will return the value -4
 Cells(1, 1).Value = nWeek
 End Sub 

Output

-4

Example 3

Sub DateDiffFunction_Example3()
 Dim datediff1 As Date
 Dim datediff2 As Date
 Dim nMinutes As Long
 datediff1 = #11/29/2015 6:00:00 PM# 
 datediff2 = #1/20/2015 7:40:00 AM#
 nMinutes = datediff("n", datediff1, datediff2)
 ' The variable nMinutes will return the value -451340
 Cells(1, 1).Value = nMinutes
 End Sub 

Output

-451340