Excel VBA DateDiff Function
2020-02-11 06:57:11

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

VBA DateDiff Function

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

VBA DateDiff Function

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

VBA DateDiff Function