DatePart VBA Function – How extract parts from date and time

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.

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

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.

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

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.

5. VisualBasic DatePart 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: