# Date Time Manipulation
# Calendar
VBA supports 2 calendars : Gregorian (opens new window) and Hijri (opens new window)
The Calendar property is used to modify or display the current calendar.
The 2 values for the Calendar are:
| Value | Constant | Description |
|---|---|---|
| 0 | vbCalGreg | Gregorian calendar (default) |
| 1 | vbCalHijri | Hijri calendar |
# Example
Sub CalendarExample()
'Cache the current setting.
Dim Cached As Integer
Cached = Calendar
' Dates in Gregorian Calendar
Calendar = vbCalGreg
Dim Sample As Date
'Create sample date of 2016-07-28
Sample = DateSerial(2016, 7, 28)
Debug.Print "Current Calendar : " & Calendar
Debug.Print "SampleDate = " & Format$(Sample, "yyyy-mm-dd")
' Date in Hijri Calendar
Calendar = vbCalHijri
Debug.Print "Current Calendar : " & Calendar
Debug.Print "SampleDate = " & Format$(Sample, "yyyy-mm-dd")
'Reset VBA to cached value.
Cached = Calendar
End Sub
This Sub prints the following ;
Current Calendar : 0
SampleDate = 2016-07-28
Current Calendar : 1
SampleDate = 1437-10-23
# Base functions
# Retrieve System DateTime
VBA supports 3 built-in functions to retrieve the date and/or time from the system's clock.
| Function | Return Type | Return Value |
|---|---|---|
| Now | Date | Returns the current date and time |
| Date | Date | Returns the date portion of the current date and time |
| Time | Date | Returns the time portion of the current date and time |
Sub DateTimeExample()
' -----------------------------------------------------
' Note : EU system with default date format DD/MM/YYYY
' -----------------------------------------------------
Debug.Print Now ' prints 28/07/2016 10:16:01 (output below assumes this date and time)
Debug.Print Date ' prints 28/07/2016
Debug.Print Time ' prints 10:16:01
' Apply a custom format to the current date or time
Debug.Print Format$(Now, "dd mmmm yyyy hh:nn") ' prints 28 July 2016 10:16
Debug.Print Format$(Date, "yyyy-mm-dd") ' prints 2016-07-28
Debug.Print Format$(Time, "hh") & " hour " & _
Format$(Time, "nn") & " min " & _
Format$(Time, "ss") & " sec " ' prints 10 hour 16 min 01 sec
End Sub
# Timer Function
The Timer function returns a Single representing the number of seconds elapsed since midnight. The precision is one hundredth of a second.
Sub TimerExample()
Debug.Print Time ' prints 10:36:31 (time at execution)
Debug.Print Timer ' prints 38191,13 (seconds since midnight)
End Sub
Because Now and Time functions are only precise to seconds, Timer offers a convenient way to increase accuracy of time measurement:
Sub GetBenchmark()
Dim StartTime As Single
StartTime = Timer 'Store the current Time
Dim i As Long
Dim temp As String
For i = 1 To 1000000 'See how long it takes Left$ to execute 1,000,000 times
temp = Left$("Text", 2)
Next i
Dim Elapsed As Single
Elapsed = Timer - StartTime
Debug.Print "Code completed in " & CInt(Elapsed * 1000) & " ms"
End Sub
# IsDate()
IsDate() tests whether an expression is a valid date or not. Returns a Boolean.
Sub IsDateExamples()
Dim anything As Variant
anything = "September 11, 2001"
Debug.Print IsDate(anything) 'Prints True
anything = #9/11/2001#
Debug.Print IsDate(anything) 'Prints True
anything = "just a string"
Debug.Print IsDate(anything) 'Prints False
anything = vbNull
Debug.Print IsDate(anything) 'Prints False
End Sub
# Extraction functions
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
# Calculation functions
# DateDiff()
DateDiff() returns a Long representing the number of time intervals between two specified dates.
Syntax
DateDiff ( interval, date1, date2 [, firstdayofweek] [, firstweekofyear] )
- interval can be any of the intervals defined in the
DatePart()(opens new window) function - date1 and date2 are the two dates you want to use in the calculation
- firstdayofweek and firstweekofyear are optional. Refer to
DatePart()(opens new window) function for explanations
Examples
Sub DateDiffExamples()
' Check to see if 2016 is a leap year.
Dim NumberOfDays As Long
NumberOfDays = DateDiff("d", #1/1/2016#, #1/1/2017#)
If NumberOfDays = 366 Then
Debug.Print "2016 is a leap year." 'This will output.
End If
' Number of seconds in a day
Dim StartTime As Date
Dim EndTime As Date
StartTime = TimeSerial(0, 0, 0)
EndTime = TimeSerial(24, 0, 0)
Debug.Print DateDiff("s", StartTime, EndTime) 'prints 86400
End Sub
# DateAdd()
DateAdd() returns a Date to which a specified date or time interval has been added.
Syntax
DateAdd ( interval, number, date )
- interval can be any of the intervals defined in the
DatePart()(opens new window) function - number Numeric expression that is the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).
- date is a
Dateor literal representing date to which the interval is added
Examples :
Sub DateAddExamples()
Dim Sample As Date
'Create sample date and time of 2016-07-28 12:34:56
Sample = DateSerial(2016, 7, 28) + TimeSerial(12, 34, 56)
' Date 5 months previously (prints 2016-02-28):
Debug.Print Format$(DateAdd("m", -5, Sample), "yyyy-mm-dd")
' Date 10 months previously (prints 2015-09-28):
Debug.Print Format$(DateAdd("m", -10, Sample), "yyyy-mm-dd")
' Date in 8 months (prints 2017-03-28):
Debug.Print Format$(DateAdd("m", 8, Sample), "yyyy-mm-dd")
' Date/Time 18 hours previously (prints 2016-07-27 18:34:56):
Debug.Print Format$(DateAdd("h", -18, Sample), "yyyy-mm-dd hh:nn:ss")
' Date/Time in 36 hours (prints 2016-07-30 00:34:56):
Debug.Print Format$(DateAdd("h", 36, Sample), "yyyy-mm-dd hh:nn:ss")
End Sub
# Conversion and Creation
# CDate()
CDate() converts something from any datatype to a Date datatype
Sub CDateExamples()
Dim sample As Date
' Converts a String representing a date and time to a Date
sample = CDate("September 11, 2001 12:34")
Debug.Print Format$(sample, "yyyy-mm-dd hh:nn:ss") ' prints 2001-09-11 12:34:00
' Converts a String containing a date to a Date
sample = CDate("September 11, 2001")
Debug.Print Format$(sample, "yyyy-mm-dd hh:nn:ss") ' prints 2001-09-11 00:00:00
' Converts a String containing a time to a Date
sample = CDate("12:34:56")
Debug.Print Hour(sample) ' prints 12
Debug.Print Minute(sample) ' prints 34
Debug.Print Second(sample) ' prints 56
' Find the 10000th day from the epoch date of 1899-12-31
sample = CDate(10000)
Debug.Print Format$(sample, "yyyy-mm-dd") ' prints 1927-05-18
End Sub
Note that VBA also has a loosely typed CVDate() that functions in the same way as the CDate() function other than returning a date typed Variant instead of a strongly typed Date. The CDate() version should be preferred when passing to a Date parameter or assigning to a Date variable, and the CVDate() version should be preferred when when passing to a Variant parameter or assigning to a Variant variable. This avoids implicit type casting.
# DateSerial()
DateSerial() function is used to create a date. It returns a Date for a specified year, month, and day.
Syntax:
DateSerial ( year, month, day )
With year, month and day arguments being valid Integers (Year from 100 to 9999, Month from 1 to 12, Day from 1 to 31).
Examples
Sub DateSerialExamples()
' Build a specific date
Dim sample As Date
sample = DateSerial(2001, 9, 11)
Debug.Print Format$(sample, "yyyy-mm-dd") ' prints 2001-09-11
' Find the first day of the month for a date.
sample = DateSerial(Year(sample), Month(sample), 1)
Debug.Print Format$(sample, "yyyy-mm-dd") ' prints 2001-09-11
' Find the last day of the previous month.
sample = DateSerial(Year(sample), Month(sample), 1) - 1
Debug.Print Format$(sample, "yyyy-mm-dd") ' prints 2001-09-11
End Sub
Note that DateSerial() will accept "invalid" dates and calculate a valid date from it. This can be used creatively for good:
Positive Example
Sub GoodDateSerialExample()
'Calculate 45 days from today
Dim today As Date
today = DateSerial (2001, 9, 11)
Dim futureDate As Date
futureDate = DateSerial(Year(today), Month(today), Day(today) + 45)
Debug.Print Format$(futureDate, "yyyy-mm-dd") 'prints 2009-10-26
End Sub
However, it is more likely to cause grief when attempting to create a date from unvalidated user input:
Negative Example
Sub BadDateSerialExample()
'Allow user to enter unvalidate date information
Dim myYear As Long
myYear = InputBox("Enter Year")
'Assume user enters 2009
Dim myMonth As Long
myMonth = InputBox("Enter Month")
'Assume user enters 2
Dim myDay As Long
myDay = InputBox("Enter Day")
'Assume user enters 31
Debug.Print Format$(DateSerial(myYear, myMonth, myDay), "yyyy-mm-dd")
'prints 2009-03-03
End Sub