VBA Date Time Manipulation 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.
| Function | Description | Returned 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 week | Integer (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 :
| Interval | Description |
|---|---|
| "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
