DatePart VBA Function - How extract parts from date and time

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)

Excel VBA functions - DatePart VBA function — Excel VBA functions – DatePart VBA function

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.