DateDiff VBA Function - How to calculate date and time difference

Last Updated on January 11, 2023 by token

1. DateDiff VBA function – Description

The DateDiff VBA function returns the difference between two dates. Depending on what we introduce in the function arguments, we can get the difference in days, months, years, weeks, quarters, hours, minutes, seconds, etc. Monday. In the English nomenclature it will be Sunday.

2. VBA DateDiff function – Syntax

DateDiff (Interval As String, Date1, Date2, [FirstDayOfWeek As VbDayOfWeek = vbSunday], [FirstWeekOfYear As VbFirstWeekOfYear = vbFirstJan1])

Interval : Specifying the unit of time in which we want the result. We put unit names in quotation marks, eg “YYYY”, if we want to get the difference in years.

Unit	Interval
Day	d
Day of the year	y
Hour	h
Minute	n
Month	m
Quarter	q
Second	s
Day of the week	in
Week of the year	above
Year	yyyy

Date1 : The date or time we are subtracting from.

Date2 : The date or time that we subtract.

FirstDayOfWeek : Define the first day of the week. In this argument, we enter a constant or its number. Below is a table of what we should introduce in this argument. The argument is optional.

Constant	Constant number	Description
vbUseSystem	0	According to system / API settings
vbSunday	1	Sunday (default)
vbMonday	2	Monday (ISO 8601)
vbTuesday	3	Tuesday
vbWednesday	4	Wednesday
vbThursday	5	Thursday
vbFriday	6	Friday
vbSaturday	7	Saturday

FirstWeekOfYear – Defining the first week of the year depending on the parameters of the function. In this argument, we enter a constant or its number. Below is a table of what we should introduce in this argument. The argument is optional.

Constant	Constant number	Description
vbUseSystem	0	According to system / API settings
vbFirstJan1	1	The week of January 1
vbFirstFourDays	2	First week of the year with at least 4 days (ISO 8601)
vbFirstFullWeek	3	The first full week of the year

3. VBA DateDiff function – Example Function return: Number / Numeric value from the difference of dates, times or dates and time.

How to use DateDiff function in VBA Excel? Below is an example of using the DateDiff function in the VisualBasic Editor.

Example 1 : VBA Date difference in days.

Dim datValue1 As Date
Dim datValue2 As Date
datValue1 = # 12/25/2017 11:45:12 AM #
datValue2 = # 11/20/2018 8:22:59 AM #
MsgBox DateDiff ("d", datValue1, datValue2, vbMonday)
Range ("A1") = DateDiff ("d", datValue1, datValue2, vbMonday)

Example 2 : VBA Difference of dates in months.

Dim datValue1 As Date
Dim datValue2 As Date
datValue1 = # 12/25/2017 11:45:12 AM #
datValue2 = # 11/20/2018 8:22:59 AM #
MsgBox DateDiff ("m", datValue1, datValue2, 2)
Range ("A2") = DateDiff ("m", datValue1, datValue2, vbMonday)

Example 3 : Date difference in VBA weeks

Dim datValue1 As Date
Dim datValue2 As Date
datValue1 = # 12/25/2017 11:45:12 AM #
datValue2 = # 11/20/2018 8:22:59 AM #
MsgBox DateDiff ("w", datValue1, datValue2,, 3)
Range ("A3") = DateDiff ("w", datValue1, datValue2,, vbFirstFourDays)

Example 4 : VBA Date difference in years.

Dim datValue1 As Date
Dim datValue2 As Date
datValue1 = # 12/25/2017 11:45:12 AM #
datValue2 = # 11/20/2018 8:22:59 AM #
MsgBox DateDiff ("yyyy", datValue1, datValue2, 2)
Range ("A4") = DateDiff ("yyyy", datValue1, datValue2, vbMonday)

Example 5 : Date and time difference in VBA hours.

Dim datValue1 As Date
Dim datValue2 As Date
datValue1 = # 12/25/2017 11:45:12 AM #
datValue2 = # 11/20/2018 8:22:59 AM #
MsgBox DateDiff ("h", datValue1, datValue2)
Range ("A5") = DateDiff ("h", datValue1, datValue2)

Example 6 : Date and time difference in VBA minutes.

Dim datValue1 As Date
Dim datValue2 As Date
datValue1 = # 12/25/2017 11:45:12 AM #
datValue2 = # 11/20/2018 8:22:59 AM #
MsgBox DateDiff ("n", datValue1, datValue2)
Range ("A6") = DateDiff ("m", datValue1, datValue2)

Example 7 : Date and time difference in VBA seconds.

Dim datValue1 As Date
Dim datValue2 As Date
datValue1 = # 12/25/2017 11:45:12 AM #
datValue2 = # 11/20/2018 8:22:59 AM #
MsgBox DateDiff ("s", datValue1, datValue2)
Range ("A7") = DateDiff ("s", datValue1, datValue2)

Example 8 : Date difference in VBA quarters.

Dim datValue1 As Date
Dim datValue2 As Date
datValue1 = # 12/25/2017 11:45:12 AM #
datValue2 = # 11/20/2018 8:22:59 AM #
MsgBox DateDiff ("Q", datValue1, datValue2)
Range ("A7") = DateDiff ("Q", datValue1, datValue2)

Excel VBA Functions - DateDiff VBA function — Excel VBA Functions – DateDiff VBA function

File with examples: functions_vba_excel_datediff.xlsm

4. VB DateDiff function – Additional information

If you want Monday to be the first day of the week while counting, enter 2 or vbMonday in the fourth argument of the function.
If we want the first week of the year to be the first full week, enter the number 3 in the fourth argument of the function.
To make the value of the date and time correctly visible to VB, enter the values in quotation marks or between hash signs ##