Date Variable in VBA Codes (7 Uses of Macros with Examples)

Date Variable Format Requirements

A date constant can be declared within hash signs (# #). It accepts two formats:

• m/d/yyyy – for example, #5/31/1993#.
• yyyy-m-d – for example, #1993-5-31#.

Declare a Date Variable Using VBA in Excel

Take a look at the following code snippets:

Sub declare_date()

Dim new_date As Date
new_date = #4/4/1996#
MsgBox new_date

End Sub

Our new_date is the date variable. We assigned a date in #d/m/yyyy# format.

When you run the code, it will show the following output:

Useful Code Snippets with a Date Variable

Example 1 – Current Date with the VBA Date Variable

You can find the current date using the following code snippet:

Sub current_date()

Dim current_date As Date
current_date = Date
MsgBox current_date

End Sub

Run the code and you will see the current date:

Example 2 – Current Time with Date Variable

You can find the current time using the following code snippet:

Sub current_time()

Dim current_time
current_time = Time
MsgBox current_time

End Sub

Output:

It will show you the current time.

Example 3 – Assign Today’s Date and Time in VBA

Use the following code in your VBA editor to show the current date and time together:

Sub current_date_time()

Dim current_date_time
current_date_time = Now
MsgBox current_date_time

End Sub

Output:

Read More: VBA to Remove Time from Date in Excel

Example 4 – The DateSerial Function to Assign a Date to Variable

You can see a date based on an input year, month, and day using The DateSerial function:

Sub date_serial()

Dim date_special As Date
date_special = DateSerial(2022, 1, 11)
MsgBox date_special

End Sub

Output:

Example 5 – The TimeSerial Function to Assign a Time to a Variable

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

Sub time_serial()

Dim tTime
tTime = TimeSerial(12, 2, 45)
MsgBox tTime

End Sub

Output:

Example 6 – The VBA DateValue Function

We can use the DateValue function to define a date:

Sub Date_value()

MsgBox Datevalue("1/11/2022")

End Sub

Run the code and you will see the following:

Example 7 – The VBA TimeValue Function

You can initialize a time using the VBA TimeValue function.

Sub Time_value()

MsgBox TimeValue("12:11:59")

End Sub

Output:

3 Examples for Using a Date Variable in Excel VBA Macros

Example 1 – Calculate Overdue Days Using a Date Variable in VBA

An assignment is due Sunday. We have a dataset of some students and their assignment submission dates. You can see the last date of submission. Our goal is to find the overdue date based on the submission date.

Steps

• Press Alt + F11 to open the VBA editor.
• Select Insert and choose Module.

• Insert the following code:

Sub overdue_days()

Dim cell As Integer
Dim J As Integer
Dim due_date As Date
due_date = #1/11/2022#

For cell = 5 To 11

 If Cells(cell, 4).Value = due_date Then

  Cells(cell, 5).Value = "Submitted Today"
 ElseIf Cells(cell, 4).Value > due_date Then
   J = due_date - Cells(cell, 4).Value
   J = Abs(J)
   Cells(cell, 5).Value = J & " Overdue Days"
 Else
   Cells(cell, 5).Value = "No Overdue"
 End If

Next cell

End Sub

We used the ABS function to remove the minus sign.

• Save the file.
• Press Alt + F8 on your keyboard to open the Macro dialog box.
• Select the Macro named as the function from the code (first line of code).
• Click on Run.

Read More: Excel VBA Textbox to Format Date

Example 2 – Find the Year of a Date

We have the date of birth of some people. Our goal is to extract the birth year from the date and also the birth year of the last entry, Elizabeth.

Steps

• Press Alt + F11 to open the VBA editor.
• Select Insert and go to Module.

• Insert the following code:

Sub find_year()

Dim last_entry As Date
Dim cell As Integer

For cell = 5 To 11
 Cells(cell, 4).Value = Year(Cells(cell, 3).Value)

 If cell = 11 Then
   last_entry = Cells(cell, 3).Value
 End If

Next cell

MsgBox "Birth Year of last entry: " & Year(last_entry)

End Sub

• Save the file.
• Press Alt + F8 to open the Macro dialog box.
• Select find_year.
• Click on Run.

Example 3 – Add Days in a Date Using the DateAdd Function in VBA

We have some names and dates attached to them. We’ll add five more days to these given dates and generate a new column.

Steps

• Press Alt + F11 to open the VBA editor.
• Select Insert and choose Module.

• Insert the following code:

Sub add_days()

Dim first_date As Date
Dim second_date As Date
Dim cell As Integer

For cell = 5 To 11

 first_date = Cells(cell, 3).Value
 second_date = DateAdd("d", 5, first_date)
 Cells(cell, 4).Value = second_date

Next cell

End Sub

We used “d” as an argument in the DateAdd function. You can change it to “y” or “m” to add years or months, respectively,

• Save the file.
• Press Alt + F8 to open the Macro dialog box.
• Select the macro name (from the first line of code).
• Click on Run.

Things to Remember

• VBA saves date values as DATE at the time of implementation.
• If you try to assign a date variable as a string/text, it will cause an error.
• The earliest value for a datetime variable is 0:00:00 (midnight) on January 1, 100.

Download the Practice Workbook