Last Updated on January 11, 2023 by token
1. DatePart VBA function – Description
The DatePart VBA function returns the individual parts of the date and time depending on the arguments entered. With its help, we can extract elements such as quarter, day of the year, week of the year, as well as standard date elements such as day, month, year, hour, minute, second. It is worth noting that these elements can also be extracted using other date functions.
2. VBA DatePart function – Syntax
DatePart (Interval As String, Date, [FirstDayOfWeek As VbDayOfWeek = vbSunday], [FirstWeekOfYear As VbFirstWeekOfYear = vbFirstJan1])
Interval : Specifying the unit of time in which we want the result. We enter unit names in quotation marks, eg “YYYY” if we want 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 |
Date : The date or time we want to use the VB DatePart function.
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 |
Function return: Number / A numeric value specifying the number of time units.
3. VBA DatePart function – Example
How to use DatePart function in VBA Excel? Below is an example of using the DatePart function in the VisualBasic Editor.
Example 1 : How to get day from VBA date.
Dim datValue1 As Date
datValue1 = # 11/20/2018 8:22:59 AM #
MsgBox DatePart ("d", datValue1, vbMonday)
Range ("A1") = DatePart ("d", datValue1, 2)
Example 2 : VBA how to extract week number from date.
Dim datValue1 As Date
datValue1 = # 11/20/2018 8:22:59 AM #
MsgBox DatePart ("ww", datValue1, 2, vbFirstFourDays)
MsgBox DatePart ("ww", datValue1, 2, vbUseSystem)
Range ("A2") = DatePart ("ww", datValue1, 2, vbFirstFourDays)
Range ("A3") = DatePart ("ww", datValue1, 2, vbUseSystem)
Example 3 : VisualBasic How to extract a month from a date.
Dim datValue1 As Date
datValue1 = # 11/20/2018 8:22:59 AM #
MsgBox DatePart ("m", datValue1)
Range ("A4") = DatePart ("m", datValue1)
Example 4 : VBA How to Extract Quarter from Date.
Dim datValue1 As Date
datValue1 = # 11/20/2018 8:22:59 AM #
MsgBox DatePart ("q", datValue1)
Range ("A5") = DatePart ("q", datValue1)
Example 5 : How to get year from VBA date.
Dim datValue1 As Date
datValue1 = # 11/20/2018 8:22:59 AM #
MsgBox DatePart ("yyyy", datValue1)
Range ("A6") = DatePart ("yyyy", datValue1)
Example 6 : How to calculate day of year number from VBA date.
Dim datValue1 As Date
datValue1 = # 11/20/2018 8:22:59 AM #
MsgBox DatePart ("y", datValue1,, vbUseSystem)
MsgBox DatePart ("y", datValue1,, vbFirstFourDays)
Range ("A7") = DatePart ("y", datValue1,, vbUseSystem)
Range ("A8") = DatePart ("y", datValue1,, vbFirstFourDays)
Example 7 : VBA Number of the day of the week.
Dim datValue1 As Date
datValue1 = # 11/20/2018 8:22:59 AM #
MsgBox DatePart ("w", datValue1, 2)
Range ("A9") = DatePart ("w", datValue1, vbMonday)
Example 8 : VBA – Calculating time from date and time.
Dim datValue1 As Date
datValue1 = # 11/20/2018 8:22:59 AM #
MsgBox DatePart ("h", datValue1)
Range ("A10") = DatePart ("h", datValue1)
Example 9 : VBA – Extracting Minutes from Date and Time.
Dim datValue1 As Date
datValue1 = # 11/20/2018 8:22:59 AM #
MsgBox DatePart ("n", datValue1)
Range ("A11") = DatePart ("n", datValue1)
Example 10 : VBA – Extracting a number of seconds from date and time.
Dim datValue1 As Date
datValue1 = # 11/20/2018 8:22:59 AM #
MsgBox DatePart ("s", datValue1)
Range ("A12") = DatePart ("s", datValue1)
File with examples: functions_vba_excel_datepart.xlsm
4. VB DatePart 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 fifth argument of the function.
- To make the value of date and time correctly visible to VB, we enter values in quotation marks or between hash signs ##.
- The numbers representing the month, day, year, hour, minute can also be obtained using such VBA functions as: Day , Month , Year , Hour , Minute, etc.
5. VisualBasic DatePart 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 .
