DateDiff VBA Function – How to calculate date and time difference

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.

UnitInterval
Dayd
Day of the yeary
Hourh
Minuten
Monthm
Quarterq
Seconds
Day of the weekin
Week of the yearabove
Yearyyyy

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.

ConstantConstant numberDescription
vbUseSystem0According to system / API settings
vbSunday1Sunday (default)
vbMonday2Monday (ISO 8601)
vbTuesday3Tuesday
vbWednesday4Wednesday
vbThursday5Thursday
vbFriday6Friday
vbSaturday7Saturday

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.

ConstantConstant numberDescription
vbUseSystem0According to system / API settings
vbFirstJan11The week of January 1
vbFirstFourDays2First week of the year with at least 4 days (ISO 8601)
vbFirstFullWeek3The 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 ##

5. DateDiff VisualBasic function – Where to use?

The function can be used in: Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016.

The article is part of the VBA Excel function list. You can find a list of all VBA functions at this address: VBA functions .

Leave a comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: