DatePart Function

Returns a Variant (Integer) containing the specified part of a given date.

Syntax

DatePart(interval, date[, firstdayofweek[, firstweekofyear]])

Parameters

• interval: Required. String expression that is the interval of time you want to return. See the Interval Settings section for valid values.
• date: Required. Variant (Date) value that you want to evaluate.
• firstdayofweek: Optional. Constant that specifies the first day of the week. If not specified, Sunday is assumed. See FirstDayOfWeek Constants.
• firstweekofyear: Optional. Constant that specifies the first week of the year. If not specified, the first week is assumed to be the week containing January 1. See FirstWeekOfYear Constants.

Interval Settings

The interval parameter can have the following values:

FirstDayOfWeek Constants

FirstWeekOfYear Constants

Return Value

Returns an Integer representing the specified part of the date. Returns Null if the date is Null.

Remarks

The DatePart function is used to extract a specific component from a date value. It's particularly useful for date-based calculations, filtering, and grouping operations.

Important Characteristics:

• More flexible than Year(), Month(), or Day() functions.
• Can extract quarter, week, and day of year.
• Weekday numbering depends on firstdayofweek parameter.
• Week numbering depends on firstweekofyear parameter.
• Hours use 24-hour format (0-23).
• Sunday is 1 by default for weekday ("w").
• Compatible with SQL Server's DATEPART function

Equivalent Simple Functions

Some intervals have equivalent dedicated functions: - DatePart("yyyy", date) = Year(date) - DatePart("m", date) = Month(date) - DatePart("d", date) = Day(date) - DatePart("w", date) = Weekday(date) - DatePart("h", date) = Hour(date) - DatePart("n", date) = Minute(date) - DatePart("s", date) = Second(date)

Examples

Basic Usage

Dim testDate As Date
testDate = #3/15/2025 14:30:45#

' Extract various parts
MsgBox "Year: " & DatePart("yyyy", testDate)      ' 2025
MsgBox "Quarter: " & DatePart("q", testDate)      ' 1
MsgBox "Month: " & DatePart("m", testDate)        ' 3
MsgBox "Day: " & DatePart("d", testDate)          ' 15
MsgBox "Day of Year: " & DatePart("y", testDate)  ' 74
MsgBox "Weekday: " & DatePart("w", testDate)      ' Varies by day
MsgBox "Week: " & DatePart("ww", testDate)        ' Week number
MsgBox "Hour: " & DatePart("h", testDate)         ' 14
MsgBox "Minute: " & DatePart("n", testDate)       ' 30
MsgBox "Second: " & DatePart("s", testDate)       ' 45

Quarter Calculation

Function GetQuarter(dateValue As Date) As Integer
GetQuarter = DatePart("q", dateValue)
End Function

' Usage
Dim currentQuarter As Integer
currentQuarter = GetQuarter(Date)
MsgBox "We are in Q" & currentQuarter

Week Number

Function GetWeekNumber(dateValue As Date) As Integer
' ISO week number (Monday start, 4-day rule)
GetWeekNumber = DatePart("ww", dateValue, vbMonday, vbFirstFourDays)
End Function

Common Patterns

Fiscal Quarter Determination

Function GetFiscalQuarter(dateValue As Date, fiscalYearStart As Integer) As Integer
' fiscalYearStart is the month number (e.g., 4 for April)
Dim currentMonth As Integer
Dim adjustedMonth As Integer

currentMonth = DatePart("m", dateValue)
adjustedMonth = currentMonth - fiscalYearStart + 1

If adjustedMonth <= 0 Then
adjustedMonth = adjustedMonth + 12
End If

GetFiscalQuarter = Int((adjustedMonth - 1) / 3) + 1
End Function

Group By Time Period

Function GroupByPeriod(dateValue As Date, period As String) As String
Select Case LCase(period)
Case "year"
GroupByPeriod = CStr(DatePart("yyyy", dateValue))
Case "quarter"
GroupByPeriod = DatePart("yyyy", dateValue) & "-Q" & DatePart("q", dateValue)
Case "month"
GroupByPeriod = DatePart("yyyy", dateValue) & "-" & Format(DatePart("m", dateValue), "00")
Case "week"
GroupByPeriod = DatePart("yyyy", dateValue) & "-W" & Format(DatePart("ww", dateValue), "00")
Case Else
GroupByPeriod = Format(dateValue, "yyyy-mm-dd")
End Select
End Function

Day Name from Weekday

Function GetDayName(dateValue As Date) As String
Select Case DatePart("w", dateValue)
Case 1: GetDayName = "Sunday"
Case 2: GetDayName = "Monday"
Case 3: GetDayName = "Tuesday"
Case 4: GetDayName = "Wednesday"
Case 5: GetDayName = "Thursday"
Case 6: GetDayName = "Friday"
Case 7: GetDayName = "Saturday"
End Select
End Function

Time of Day Category

Function GetTimeOfDay(dateValue As Date) As String
Dim hour As Integer
hour = DatePart("h", dateValue)

Select Case hour
Case 0 To 5
GetTimeOfDay = "Night"
Case 6 To 11
GetTimeOfDay = "Morning"
Case 12 To 17
GetTimeOfDay = "Afternoon"
Case 18 To 23
GetTimeOfDay = "Evening"
End Select
End Function

Business Hour Check

Function IsBusinessHours(checkTime As Date) As Boolean
Dim hour As Integer
Dim weekday As Integer

hour = DatePart("h", checkTime)
weekday = DatePart("w", checkTime)

' Monday-Friday, 9 AM - 5 PM
If weekday >= 2 And weekday <= 6 Then  ' Mon-Fri
If hour >= 9 And hour < 17 Then
IsBusinessHours = True
End If
End If
End Function

Month Name Lookup

Function GetMonthName(dateValue As Date) As String
Dim monthNames As Variant
Dim monthNum As Integer

monthNames = Array("January", "February", "March", "April", "May", "June", _
"July", "August", "September", "October", "November", "December")

monthNum = DatePart("m", dateValue)
GetMonthName = monthNames(monthNum - 1)
End Function

Quarter End Date

Function GetQuarterEnd(dateValue As Date) As Date
Dim quarter As Integer
Dim year As Integer
Dim endMonth As Integer

quarter = DatePart("q", dateValue)
year = DatePart("yyyy", dateValue)
endMonth = quarter * 3

GetQuarterEnd = DateSerial(year, endMonth + 1, 0)  ' Last day of quarter
End Function

Data Binning by Hour

Function GetHourBucket(timestamp As Date) As String
Dim hour As Integer
hour = DatePart("h", timestamp)
GetHourBucket = Format(hour, "00") & ":00"
End Function

' Use for grouping log entries
Sub AnalyzeLogs()
Dim entry As Date
Dim bucket As String

For Each entry In logEntries
bucket = GetHourBucket(entry)
hourCounts(bucket) = hourCounts(bucket) + 1
Next
End Sub

Advanced Usage

ISO 8601 Week Number

Function GetISOWeekNumber(dateValue As Date) As Integer
' ISO 8601 week number: Monday start, 4-day rule
GetISOWeekNumber = DatePart("ww", dateValue, vbMonday, vbFirstFourDays)
End Function

Function GetISOYear(dateValue As Date) As Integer
' Year for ISO week (may differ from calendar year)
Dim weekNum As Integer
Dim month As Integer

weekNum = GetISOWeekNumber(dateValue)
month = DatePart("m", dateValue)

If month = 1 And weekNum > 51 Then
GetISOYear = DatePart("yyyy", dateValue) - 1
ElseIf month = 12 And weekNum = 1 Then
GetISOYear = DatePart("yyyy", dateValue) + 1
Else
GetISOYear = DatePart("yyyy", dateValue)
End If
End Function

Dynamic Date Grouping

Function GetDateKey(dateValue As Date, granularity As String) As String
Dim year As Integer
Dim month As Integer
Dim day As Integer
Dim week As Integer
Dim quarter As Integer

year = DatePart("yyyy", dateValue)

Select Case LCase(granularity)
Case "year"
GetDateKey = CStr(year)

Case "quarter"
quarter = DatePart("q", dateValue)
GetDateKey = year & "Q" & quarter

Case "month"
month = DatePart("m", dateValue)
GetDateKey = year & Format(month, "00")

Case "week"
week = DatePart("ww", dateValue, vbMonday)
GetDateKey = year & "W" & Format(week, "00")

Case "day"
month = DatePart("m", dateValue)
day = DatePart("d", dateValue)
GetDateKey = year & Format(month, "00") & Format(day, "00")

Case Else
GetDateKey = Format(dateValue, "yyyymmdd")
End Select
End Function

Custom Calendar System

Type CustomCalendar
Year As Integer
Period As Integer
Week As Integer
Day As Integer
End Type

Function ConvertToCustomCalendar(dateValue As Date) As CustomCalendar
Dim cal As CustomCalendar
Dim yearStart As Date
Dim dayOfYear As Integer

cal.Year = DatePart("yyyy", dateValue)

' 13 periods of 4 weeks each
yearStart = DateSerial(cal.Year, 1, 1)
dayOfYear = DatePart("y", dateValue)

cal.Week = Int((dayOfYear - 1) / 7) + 1
cal.Period = Int((cal.Week - 1) / 4) + 1
cal.Day = DatePart("w", dateValue, vbMonday)

ConvertToCustomCalendar = cal
End Function

Time Series Aggregation

Function AggregateByInterval(dates() As Date, values() As Double, _
interval As String) As Collection
Dim results As New Collection
Dim i As Long
Dim key As String
Dim total As Double
Dim count As Long

For i = LBound(dates) To UBound(dates)
key = GetDateKey(dates(i), interval)

On Error Resume Next
total = results(key)
If Err.Number <> 0 Then
results.Add values(i), key
Else
results.Remove key
results.Add total + values(i), key
End If
On Error GoTo 0
Next i

Set AggregateByInterval = results
End Function

Shift Schedule Detector

Function GetShift(timestamp As Date) As String
Dim hour As Integer
Dim weekday As Integer

hour = DatePart("h", timestamp)
weekday = DatePart("w", timestamp)

' Weekend check
If weekday = 1 Or weekday = 7 Then
GetShift = "Weekend"
Exit Function
End If

' Shift determination
Select Case hour
Case 6 To 13
GetShift = "Morning Shift"
Case 14 To 21
GetShift = "Afternoon Shift"
Case Else
GetShift = "Night Shift"
End Select
End Function

Calendar Week Display

Function FormatCalendarWeek(dateValue As Date, Optional useISO As Boolean = False) As String
Dim year As Integer
Dim week As Integer

If useISO Then
year = GetISOYear(dateValue)
week = GetISOWeekNumber(dateValue)
Else
year = DatePart("yyyy", dateValue)
week = DatePart("ww", dateValue)
End If

FormatCalendarWeek = year & "-W" & Format(week, "00")
End Function

Error Handling

Function SafeDatePart(interval As String, dateValue As Variant) As Variant
On Error GoTo ErrorHandler

' Validate date
If Not IsDate(dateValue) Then
SafeDatePart = Null
Exit Function
End If

' Validate interval
Select Case LCase(interval)
Case "yyyy", "q", "m", "y", "d", "w", "ww", "h", "n", "s"
SafeDatePart = DatePart(interval, CDate(dateValue))
Case Else
SafeDatePart = Null
End Select

Exit Function

ErrorHandler:
SafeDatePart = Null
End Function

Common Errors

• Error 5 (Invalid procedure call): Invalid interval string
• Error 13 (Type mismatch): Non-date value passed as date parameter

Performance Considerations

• DatePart is efficient for single extractions
• For multiple parts from same date, consider using dedicated functions:

' Less efficient
y = DatePart("yyyy", d)
m = DatePart("m", d)
d = DatePart("d", d)

' More efficient
y = Year(d)
m = Month(d)
d = Day(d)

• Week calculations are more expensive than other intervals
• Cache results when processing large datasets

Best Practices

Use Named Constants

' Define interval constants
Const INTERVAL_YEAR As String = "yyyy"
Const INTERVAL_QUARTER As String = "q"
Const INTERVAL_MONTH As String = "m"
Const INTERVAL_WEEK As String = "ww"

' Use in code
quarter = DatePart(INTERVAL_QUARTER, Date)

Prefer Specific Functions for Simple Cases

' Good - Use specific function
y = Year(someDate)

' Less clear - Using DatePart
y = DatePart("yyyy", someDate)

Be Aware of Weekday Numbering

' Default: Sunday = 1
day = DatePart("w", Date)

' Explicit: Monday = 1
day = DatePart("w", Date, vbMonday)

Comparison with Other Functions

DatePart vs Dedicated Functions

' DatePart - Flexible, supports all intervals
quarter = DatePart("q", Date)
dayOfYear = DatePart("y", Date)

' Dedicated - Simpler, more readable for common cases
year = Year(Date)
month = Month(Date)
day = Day(Date)
weekday = Weekday(Date)

Limitations

• No millisecond support
• Week numbering can be confusing with different standards (ISO vs US)
• Quarter calculation doesn't support fiscal quarters directly
• No built-in locale-aware day/month names
• FirstWeekOfYear affects week numbering interpretation

Related Functions

• Year: Returns the year part of a date
• Month: Returns the month part of a date
• Day: Returns the day part of a date
• Weekday: Returns the day of the week
• Hour: Returns the hour part of a time
• Minute: Returns the minute part of a time
• Second: Returns the second part of a time
• DateAdd: Adds a time interval to a date
• DateDiff: Returns the difference between two dates
• DateSerial: Creates a date from year, month, and day values
• Format: Formats a date as a string (alternative for custom formatting)