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 |