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

In Microsoft Excel’s VBA, there are a lot of essential methods and functions. To use those, we have to declare variables. The Date variable is one of them. If you want to make these come in handy, you should know every detail about the variables. In this tutorial, you will learn about the Date Variable of VBA in Excel with suitable examples and proper illustrations.

Date Variable Format Requirements

Firstly, we have to declare a Date literal within number signs (# #). We must determine the date value in the format M/d/yyyy. For example, #5/31/1993#. Another way you can do this is the yyyy-MM-dd format. For example, #1993-5-31#. You can use slashes when defining the year first. This condition is separate from your area and your computer’s date and time format settings.

The explanation for this limitation is that the purpose of your code should never change relying on the area in which your application is operating. Assume you hard code a Date value of #3/4/1998# and plan it to imply March 4, 1998. In a zone that utilizes mm/dd/yyyy, 3/4/1998 compiles as you plan. But imagine you deploy your application in many countries/provinces. In a place that uses dd/mm/yyyy, your hard-coded literal would assemble to April 3, 1998. In a spot that uses yyyy/mm/dd, the literal would be invalid (April 1998, 0003) and cause a compiler error.

Declare Date Variable Using VBA in Excel

Now, declaring a Date variable is pretty simple. In VBA, you have to declare a date variable with the name of “Date”. It is like defining variables like Integer, Double, Range, etc.

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

Here, our new_date is the date variable. And the type is Date. After that, we assigned a date in #d/m/yyyy# format.

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

As you can see, we have successfully used the Date variable using VBA in Excel.

Useful Code Snippets of Date Variable

In this section, we are going to provide you with some useful code snippets that you can implement into your worksheets. We recommend you learn and apply all of these. Surely, it will come in handy in the future and develop your Excel knowledge.

1. Current Date with VBA Date Variable

Now, 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:

2. Current Time with Date Variable

Also, 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.

3. Impute Today’s Date and Time in VBA

Type 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

4. 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:

5. TimeSerial Function to Assign a Time to 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:

6. VBA DateValue Function

Now, we use the DateValue function to define a date. Have a look at the following code snippet to have a better understanding:

Sub Date_value()

MsgBox Datevalue("1/11/2022")

End Sub

Run the code and you will see the following:

7. VBA TimeValue Function

Similarly, you can initialize a time using the VBA TimeValue function.

Sub Time_value()

MsgBox TimeValue("12:11:59")

End Sub

Output:

As you can see, we have demonstrated all the operations of Date variables in VBA.

3 Examples with Date Variable in Excel VBA Macros

In the following sections, we will provide you with three practical and suitable examples of the date variable. These examples will contain date-related problems and their solutions. We recommend you read and practice all these examples to improve your VBA knowledge. Let’s get into it.

1. Calculate Overdue Days Using Date Variable in VBA

The meaning of overdue is happening late or past the deadline. Late, particularly, past a deadline or too late to meet a requirement.

Suppose you have to submit an assignment due Sunday. But it is Tuesday, and you haven’t submitted it. You can call it two overdue days.

Take a look at the following dataset:

Here, 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. Now, to accomplish this, follow these steps:

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, select Insert>Module.

• After that, type 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.

• Then, save the file.
• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select
• Then, click on Run.

As you can see, we have successfully used the date variable in VBA and found the overdue days.

Read more: Excel VBA Textbox to Format Date

2. Find the Year of a Date

Now, you can find the Year from a particular date. This is pretty simple to find.

Take a look at the following dataset:

Here, you can see the date of birth of some persons. Our goal is to extract the birth year from the date and also the birth year of the last entry Elizabeth.

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, select Insert>Module.

• After that, type 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

• Then, save the file.
• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select find_year.
• Then, click on Run.

Finally, you can see we are successful in extracting the birth year from each date. Also, we found the birth year of the last entry using the date variable of VBA in Excel.

3. Add Days in a Date Using DateAdd Function in VBA

Now, you can define the date variable and use it to add the date. To perform this, we are using the DateAdd method of VBA. You can use this function to add days, months, and years to a particular date.

Take a look at the following dataset:

Here, you can see some names and some dates attached to them. Now, our goal is to add five more days to these given dates and generate a new date.

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, select Insert>Module.

• After that, type 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

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

• Then, save the file.
• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select.
• Then, click on Run.

As you can see, we have successfully added days into a date using the date variable in VBA. Now, you can modify the code according to your choice.

💬 Things to Remember

✎ Basically, VBA saves Date values as DATE at the time of implementation.

✎ So, if you try to assign a date variable as a string/text, it will cause an error.

✎ The default value of Date is 0:00:00 (midnight) on January 1, 0001.

Download Practice Workbook

Download this practice workbook.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge about the Date Variable using VBA in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Keep learning new methods and keep growing!