The VBA DatePart Function

Related Functions:

VBA Day
VBA Month
VBA Year
VBA Weekday
VBA Hour
VBA Minute
VBA Second

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

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

Return to the VBA Functions Page
Return to the Excel VBA Tutorial Page