The VBA DATEDIFF Function

Related Function:

Description

The VBA DateDiff Function returns a Long data value representing the number of intervals between two supplied dates/times. The type of interval (e.g. hours, days, months, etc.) is specified by the user.

The syntax of the DateDiff function is:

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

Where the function arguments are:

Interval

A string specifying the interval to be used. This can have any of the following values:

"d"	-	Days
"h"	-	Hours
"n"	-	Minutes
"m"	-	Months
"q"	-	Quarters (of a Year)
"s"	-	Seconds
"w"	-	Weeks
"ww"	-	Calendar Weeks
"yyyy"	-	Year

Date1

A date value, representing the start date/time for the calculation.

Date2

A date value, representing the end date/time for the calculation.

[FirstDayOfWeek]

An optional FirstDayOfWeek enumeration value, specifying the weekday that should be used as the first day of the week.

This can have any of the following values:

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

If omitted, the [FirstDayOfWeek] argument uses the default value vbSunday.

[FirstWeekOfYear]

An optional FirstWeekOfYear enumeration value, specifying the week that should be used as the first week of the year.

This can have any of the following values:

vbSystem	-	The first week of the year is as specified in your system settings
vbFirstJan1	-	The week in which Jan 1st occurs
vbFirstFourDays	-	The first week that contains at least four days in the new year
vbFirstFullWeek	-	The first full week in the new year

If omitted, the [FirstWeekOfYear] argument uses the default value vbFirstJan1.

VBA DateDiff Function Examples

Example 1

' Calculate the number of days between 11/29/2015 and 12/31/2015

								Dim dt1 As Date

								Dim dt2 As Date

								Dim nDays As Long

								dt1 = #11/29/2015#

								dt2 = #12/31/2015#

								nDays = DateDiff( "d", dt1, dt2 )

' nDays now has the value 32

In the above example, the DateDiff function calculates the number of days between the dates 11/29/2015 and 12/31/2015 and returns the result 32.

I.e. there are 32 days between the two supplied dates.

Example 2

' Calculate the number of hours between 11/29/2015 9:00 and 11/30/2015 12:00

								Dim dt1 As Date

								Dim dt2 As Date

								Dim nHours As Long

								dt1 = #11/29/2015 9:00:00 AM#

								dt2 = #11/30/2015 12:00:00 PM#

								nHours = DateDiff( "h", dt1, dt2 )

' nHours now has the value 27

In the above example, the DateDiff function calculates the number of hours between 09:00 on 11/29/2015 and 12:00 on 11/30/2015 and returns the result 27.

Example 3

If the Interval argument is specified to be "w" (weeks), the DateDiff function returns the number of whole weeks between the two supplied dates. Partial weeks are ignored.

This is illustrated in the following example:

' Calculate the number of weeks between 11/29/2015 and 12/8/2015

								Dim dt1 As Date

								Dim dt2 As Date

								Dim nWeeks As Long

								dt1 = #11/29/2015#

								dt2 = #12/8/2015#

								nWeeks = DateDiff( "w", dt1, dt2 )

' nWeeks now has the value 1

In the above example, the DateDiff function calculates the number of whole weeks between the dates 11/29/2015 and 12/8/2015 and returns the result 1.

I.e. there is one whole week between the two supplied dates.

Example 4

If the Interval argument is specified to be "ww" (calendar weeks), the DateDiff function returns the number of weeks between the start of the week containing the supplied Date1 and the start of the week containing the supplied Date2.

This is illustrated in the following example:

								' Calculate the number of calendar weeks between 11/29/2015 and 12/8/2015

								' First day of the week = Monday

								Dim dt1 As Date

								Dim dt2 As Date

								Dim nWeeks As Long

								dt1 = #11/29/2015#

								dt2 = #12/8/2015#

								nWeeks = DateDiff( "ww", dt1, dt2, vbMonday )

' nWeeks now has the value 2

After running the above section of VBA code, the variable nWeeks has the value 2.

I.e. when Monday is specified as the first day of the week, there are two weeks between the start of the week containing the date 11/29/2015 and the start of the week containing the date 12/8/2015.

Example 5

If the interval is specified to be months, quarters or years, the DateDiff function simply subtracts the months, quarters and/or years during which each of the supplied dates occur.

Therefore,the function will return 1 for the number of months between the last day of November 2015 and the first day of December 2015, but will return 0 for the number of months between the first and last day of December 2015.

This is shown in the following example:

								' Calculate the number of months between 11/30/2015 and 12/1/2015

									' and the number of months between 12/1/2015 and 12/31/2015

								Dim nMths1 As Long

								Dim nMths2 As Long

								nMths1 = DateDiff( "m", #11/30/2015#, #12/1/2015# )

								nMths2 = DateDiff( "m", #12/1/2015#, #12/31/2015# )

' nMths1 now has the value 1 and nMths2 has the value 0

After running the above section of VBA code, the variable nMths1 has the value 1 and the variable nMths2 has the value 0.

Return to the VBA Functions Page
Return to the Excel VBA Tutorial Page