Excel VBA DatePart Function

The DatePart function in VBA returns a part (day, month, week, etc.) for the specified date and/or time.

Syntax

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

Parameter

Interval (required) – This parameter represents a string specifying the interval to be used.

It can take the following values:

• “d” – Day of month (1-31)
• “h” - Hours
• “n” - Minutes
• “m” - Months
• “q” - Quarters
• “s” - Seconds
• “ww” – Weeks
• “yyyy” - Years

Date1 (required) – This parameter represents a date value, returning a part of it.

FirstDayOfWeek (optional) – This parameter represents the weekday that should be used as the first day of the week.

It can take the following values:

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

FirstWeekOfYear (optional) - This parameter represents the week that should be used as the first week of the year. The default value is set to vbFirstJan1.

It can take the following values:

• vbSystem – It signifies the first week of the year is as specified in your system settings
• vbFirstJan1 – It signifies the week in which Jan 1st occurs.
• vbFirstFourDays – It signifies the first week that contains at least four days in the new year.
• vbFirstFourDays – It signifies the first full week in the new year

Return

Example 1

Sub DatePartFunction_Example1()
 ' Returning the day, month & year from the specified date
 Dim day_val As Integer
 Dim month_val As Integer
 Dim year_val As Integer 
 day_val = DatePart("d", #12/1/2020#)
 month_val = DatePart("m", #12/31/2020#)
 year_val = DatePart("yyyy", #2/9/2020#)
 ' The variables will return day_val = 01, month_val = 12 and year_val = 2020. 
 Cells(1, 1).Value = day_val
 Cells(2, 1).Value = month_val
 Cells(3, 1).Value = year_val
 End Sub 

Output

1

12

2020

Example 2

Sub DatePartFunction_Example2()
 ' Returning the hour, minute & second from the specified date
 Dim hour_val As Integer
 Dim month_val As Integer
 Dim Sec_val As Integer
 hour_val = DatePart("h", #9:15:50 PM#)
 Min_val = DatePart("n", #8:05:30 PM#)
 Sec_val = DatePart("s", #9:35:40 PM#)
 ' The variables will return hour_val = 21, Min_val = 5 and Sec_val = 40.
 Cells(1, 1).Value = hour_val
 Cells(2, 1).Value = Min_val
 Cells(3, 1).Value = Sec_val
 End Sub 

Output

21

5

40

Example 3

Sub DatePartFunction_Example2()
 ' Returning Year, Day of Week, Week of Year & Quarter from the Date
 Dim dayYear As Integer
 Dim dayWeek As Integer
 Dim weekYear As Integer
 Dim quatr As Integer
 dayYear = DatePart("y", #12/3/2020#)
 dayWeek = DatePart("w", #12/9/2020#) 
 weekYear = DatePart("ww", #12/12/2020#)
 quatr = DatePart("q", #12/31/2020#)
 ' The variables will return dayYear = 338, dayWeek = 4, weekYear = 50, quatr = 4
 Cells(1, 1).Value = dayYear
 Cells(2, 1).Value = dayWeek 
 Cells(3, 1).Value = weekYear
 Cells(4, 1).Value = quatr
 End Sub 

Output

338

4

50

4