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)
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, Excel 2019, Excel 2021, Excel 365.
The article is part of the VBA Excel function list. You can find a list of all VBA functions at this address: VBA functions .
