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
VBA DatePart Function

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
VBA DatePart Function