The VBA DatePart Function

Description

The VBA DatePart Function returns a part (day, month, week, etc.) of a supplied date and/or time.

The syntax of the function is:

Month( Interval, Date, [FirstDayOfWeek], [FirstWeekOfYear] )

Where the function arguments are:

Interval -

A string specifying the date part to be returned. This can have any of the following values:

"d" - Day of Month (1 - 31)
"h" - Hour
"m" - Month
"n" - Minute
"q" - Quarter
"s" - Second
"w" - Day of Week (1 - 7)
"ww" - Week of Year (1 - 53)
"y" - Day of Year (1 - 366)
"yyyy" - Year
Date - The date value that you want to return a part of.
[FirstDayOfWeek] -

An optional FirstDayOfWeek enumeration value, specifying the weekday that should be used as the first day of the week.

This can have any of the following values:

vbUseSystemDayOfWeek - The first day of the week is as specified in your system settings
vbSunday - Sunday
vbMonday - Monday
vbTuesday - Tuesday
vbWednesday - Wednesday
vbThursday - Thursday
vbFriday - Friday
vbSaturday - Saturday

If omitted, the [FirstDayOfWeek] argument uses the default value vbSunday.

[FirstWeekOfYear] -

An optional FirstWeekOfYear enumeration value, specifying the week that should be used as the first week of the year.

This can have any of the following values:

vbSystem - The first week of the year is as specified in your system settings
vbFirstJan1 - The week in which Jan 1st occurs
vbFirstFourDays - The first week that contains at least four days in the new year
vbFirstFullWeek - The first full week in the new year

If omitted, the [FirstWeekOfYear] argument uses the default value vbFirstJan1.



VBA DatePart Function Examples

Example 1 - Extract Day, Month & Year From a Date

' Return the day, month & year from the date 12/31/2015
Dim dy As Integer
Dim mth As Integer
Dim yr As Integer
dy = DatePart( "d", #12/31/2015# )
mth = DatePart( "m", #12/31/2015# )
yr = DatePart( "yyyy", #12/31/2015# )
' Now, dy = 31, mth = 12 and yr = 2015.

After running the above VBA code, the variables dy, mth and yr are equal to 31, 12 and 2015, respectively.


Example 2 - Extract Hour, Minute & Seconds from a Time

' Return hour, minute and seconds from the time 3:05:30 PM
Dim hr As Integer
Dim min As Integer
Dim sec As Integer
hr = DatePart( "h", #3:05:30 PM# )
min = DatePart( "n", #3:05:30 PM# )
sec = DatePart( "s", #3:05:30 PM# )
' Now, hr = 15, min = 5 and sec = 30.

After running the above VBA code, the variables hr, min and sec are equal to 15, 5 and 30, respectively.


Example 3 - Extract Day of Year, Day of Week, Week of Year & Quarter From a Date

' Return day, week & quarter info. from the date 12/31/2015
Dim dyYr As Integer
Dim dyWk As Integer
Dim wkYr As Integer
Dim qtr As Integer
dyYr = DatePart( "y", #12/31/2015# )
dyWk = DatePart( "w", #12/31/2015# )
wkYr = DatePart( "ww", #12/31/2015# )
qtr = DatePart( "q", #12/31/2015# )
' Now, dyYr = 365, dyWk = 5, wkYr = 53 and qtr = 4.

After running the above VBA code, dyYr = 365; dyWk = 5 (Thursday); wkYr = 53 and qtr = 4.

Note that the [FirstDayOfWeek] and [FirstWeekOfYear] arguments have been omitted from the above function calls and so these arguments use the default values vbSunday and vbFirstJan1 respectively.


VBA DatePart Function Errors

If the Interval argument that is supplied to the VBA DatePart function is not a valid interval (i.e. is not one of the values in the above list), the DatePart function returns the error:

Run-time error '5': Invalid procedure call or argument

VBA Run Time Error 5 Message Box


If the Date argument that is supplied to the VBA DatePart function cannot be recognised as a valid VBA date or time, the function returns the error:

Run-time error '13': Type mismatch

VBA Run Time Error 13 Message Box