VBA Extraction functions


Example

These functions take a Variant that can be cast to a Date as a parameter and return an Integer representing a portion of a date or time. If the parameter can not be cast to a Date, it will result in a run-time error 13: Type mismatch.

FunctionDescriptionReturned value
Year()Returns the year portion of the date argument.Integer (100 to 9999)
Month()Returns the month portion of the date argument.Integer (1 to 12)
Day()Returns the day portion of the date argument.Integer (1 to 31)
WeekDay()Returns the day of the week of the date argument. Accepts an optional second argument definining the first day of the weekInteger (1 to 7)
Hour()Returns the hour portion of the date argument.Integer (0 to 23)
Minute()Returns the minute portion of the date argument.Integer (0 to 59)
Second()Returns the second portion of the date argument.Integer (0 to 59)

Examples:

Sub ExtractionExamples()

    Dim MyDate As Date
    
    MyDate = DateSerial(2016, 7, 28) + TimeSerial(12, 34, 56)

    Debug.Print Format$(MyDate, "yyyy-mm-dd hh:nn:ss") ' prints 2016-07-28 12:34:56

    Debug.Print Year(MyDate)                           ' prints 2016
    Debug.Print Month(MyDate)                          ' prints 7
    Debug.Print Day(MyDate)                            ' prints 28
    Debug.Print Hour(MyDate)                           ' prints 12
    Debug.Print Minute(MyDate)                         ' prints 34
    Debug.Print Second(MyDate)                         ' prints 56
    
    Debug.Print Weekday(MyDate)                        ' prints 5
    'Varies by locale - i.e. will print 4 in the EU and 5 in the US
    Debug.Print Weekday(MyDate, vbUseSystemDayOfWeek)
    Debug.Print Weekday(MyDate, vbMonday)              ' prints 4
    Debug.Print Weekday(MyDate, vbSunday)              ' prints 5
    
End Sub

DatePart() Function

DatePart() is also a function returning a portion of a date, but works differently and allow more possibilities than the functions above. It can for instance return the Quarter of the year or the Week of the year.

Syntax:

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

interval argument can be :

IntervalDescription
"yyyy"Year (100 to 9999)
"y"Day of the year (1 to 366)
"m"Month (1 to 12)
"q"Quarter (1 to 4)
"ww"Week (1 to 53)
"w"Day of the week (1 to 7)
"d"Day of the month (1 to 31)
"h"Hour (0 to 23)
"n"Minute (0 to 59)
"s"Second (0 to 59)

firstdayofweek is optional. it is a constant that specifies the first day of the week. If not specified, vbSunday is assumed.

firstweekofyear is optional. it is a constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.

Examples:

Sub DatePartExample()

    Dim MyDate As Date
    
    MyDate = DateSerial(2016, 7, 28) + TimeSerial(12, 34, 56)

    Debug.Print Format$(MyDate, "yyyy-mm-dd hh:nn:ss") ' prints 2016-07-28 12:34:56
    
    Debug.Print DatePart("yyyy", MyDate)              ' prints 2016
    Debug.Print DatePart("y", MyDate)                 ' prints 210
    Debug.Print DatePart("h", MyDate)                 ' prints 12
    Debug.Print DatePart("Q", MyDate)                 ' prints 3
    Debug.Print DatePart("w", MyDate)                 ' prints 5
    Debug.Print DatePart("ww", MyDate)                ' prints 31

End Sub