VBA Dates

VBA's built-in Date data type provides an easy native way to deal with dates.

Date Literals

Date literals in VBA are date expressions which can be a time, date, or datetime surrounded by the # sign.

Examples of Date Literals

#01/01/2020#
#01/01/2020 00:00:00#
#01/01/2020 12:00:00 AM#
#12:00:00 AM# -> Default date when only time is specified will be 12/30/1899.

Current Date and Time

The Date function returns the current date.

The Time function returns the current time.

The Now function returns the current date and time.

Public Sub Example()

    Debug.Print "The current date is: " & Date

    Debug.Print "The current time is: " & Time

    Debug.Print "The current date and time is: " & Now

End Sub

Date Conversion

The CDate function is used to convert an expression to a date.

The CVDate function converts a date expression to a Variant of subtype Date. CVDate exists for backward compatibility because VBA did not always have a Date type. CDate should be used instead.

Public Sub Example()

    Dim D As Date    
    D = CDate("Jan 1 2021")

End Sub

The IsDate function is used to check if an expression is a date.

Public Sub Example()
    
    If IsDate("Jan 1 2021") Then
        Debug.Print "Is Date"
    Else
        Debug.Print "Is Not Date"
    End If

End Sub

Format Dates

The Format function can be used to get a date as a formatted string.

Public Sub Example()

    Debug.Print Format$(Now, "mmmm d, yyyy at h:mm AM/PM")

End Sub

Format	Result
ddmmyyyy	01012021
dd/mm/yyyy	01/01/2021
dd/mm/yyyy hh:mm:ss	01/01/2021 00:00:00
dd mmmm yyyy	01 January 2021

DateTime Module

The built-in DateTime module contains a number of functions to facilitate working with dates.

Member	Description
Calendar	Sets the type of calendar. vbCalGreg or vbCalHijri.
Date	Returns the current system date as a Variant/Date.
Date$	Returns the current system date as a String.
Now	Returns the current system date and time as a Variant/Date.
Time	Returns the current system time as a Variant/Date.
Time$	Returns the current system time as a String.
Timer	Returns the number of elapsed seconds since midnight as a Single.
DateAdd	Returns a date with a given time interval added as a Variant/Date.
DateDiff	Returns the quantity of a given time interval between two given dates as a Variant/Long.
DatePart	Returns part of a given date as a Variant/Integer. *There is a bug in this function. The last Monday in some years can be returned as week 53 when it should be week 1. See here.
DateSerial	Returns a Variant/Date for a given year, month, and day.
DateValue	Returns a Variant/Date for a given string representing a date from 1/1/100 through 12/31/9999.
Day	Returns the day of the month as a Variant/Integer.
Hour	Returns the hour of the day as a Variant/Integer.
Minute	Returns the minute of the hour as a Variant/Integer.
Month	Returns the month of the year as a Variant/Integer.
Second	Returns the second of the minute as a Variant/Integer.
TimeSerial	Returns the time for a specific hour, minute, and second as a Variant/Date.
TimeValue	Returns the time given a string representing a time from 0:00:00 through 23:59:59 as a Variant/Date.
Weekday	Returns a whole number representing the day of the week as a Variant/Integer.
WeekDayName	Returns the name of the weekday given a weekday number. *Not part of DateTime module. Actually member of Strings module.
Year	Returns a whole number representing the year as a Variant/Integer.