VBA Date Variable

Associated Files Download Links

We have already gone over what variables, data types and constants are, in our VBA Data Types – Variables and Constants tutorial. In this tutorial, we are going to cover how to declare a variable as a date.

In VBA, the date data type is stored as a decimal. Both dates and times can be stored in this data type. The VBA date data type can store values ranging from 1 January  100 – 31 December 9999.

VBA Date Variable

You declare a variable as a date using the Dim keyword:

Dim dateOne as Date

Then you can assign a date to the date variable.  There are multiple ways to generate dates to assign to variables:

Assign Today to Variable

The Date Function will return today’s date:

Dim dtToday

dtToday = Date

Assign Now to Variable

The Now Function will return today’s date and current time:

Dim dNow

dNow = Now

Assign Time to Variable

The Time Function will return the current time:

Dim tTime 

tTime = Time

Assign a Date to Variable with DateSerial

The DateSerial function will return a date based on an input year, month and day:

Dim dsDate 

dsDate = DateSerial(2010, 11, 11)

Assign a Time to Variable with TimeSerial

The TimeSerial function will return a time, based on an input hour, minute and second:

Dim tsTime

tsTime = TimeSerial (10, 10, 45)

You must enclose dates with the # or ” when using them in your code, as shown below:

VBA DateValue Function

The VBA DateValue Function can be used to initialize a date. The following code can be used to initialize a date in VBA:

MsgBox DateValue("1/1/2010")

VBA TimeValue Function

MsgBox TimeValue("21:30:57")

Excel VBA Date Variable Example

This procedure demonstrates how to create date variables, assign them values, and output them into an Excel worksheet:

Sub DeclaringAVariableAsADate()

Dim dateOne As Date
Dim dateTwo As Date

dateOne = #1/1/2019#
dateTwo = "1/2/2019"

Range("A1").Value = dateOne
Range("A2").Value = dateTwo

End Sub

The result is:

Declaring a Date variable in VBA

You can learn more about Date Functions in this tutorial.