VBA Date Function

Updated on June 6, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA DATE Function

VBA Date is a Date and Time function. Therefore, it returns only the current date as per your system date. Also, the important thing to note is that this function has no arguments whatsoever. Therefore, another important factor is that this function returns the current system date.

In Excel, we cannot live without some of the functions, and “VBA Date” is one of those functions. In addition, if you are a frequent user of an Excel worksheet, then you must be aware of a function called “TODAY (),” which will return the current date as per the system date.

The Date function is simple. It returns only the current date per the system date you are using. It works similar to our worksheet function “TODAY” but not volatile in nature.

The syntax of the excel DATE function is very simple because it has no argument to supply and includes only empty parenthesis.

Date ()

Parenthesis is available to explain the function when you use the function. No need to enter parenthesis.

VBA Date Function

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Date Function (wallstreetmojo.com)

How to use Excel VBA Date Function?

You can download this VBA Date Excel Template here – VBA Date Excel Template

Example #1

Assume you want to insert the current date in cell A1 then follow the below steps to write the code to insert the current date in cell A1.

Step 1: Create a macro name.


Sub Date_Example1()
VBA Date Function Example 1

Step 2: Since we need to store the current date in cell A1 our code will be Range (“A1”).Value.


Sub Date_Example1()
End Sub
VBA Date Function Example 1-1

Step 3: In cell A1 we need the current date, so use the Date function.


Sub Date_Example1()
Range("A1").Value = Date
End Sub
VBA Date Function Example 1-1

Step 4: We have completed it now. Let us run this code now by pressing the F5 key, or we can also run the code manually, as shown in the below screenshot. We will get the current date in cell A1.

VBA Date Function Example 1-2

So, when writing the code, the current date in the system is “15th March 2019.”

Note: The format of your date depends on your windows settings. Anyway, you can change the format of the date under format cells.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Example #2

Assume you are a LIC agent and have several customers to deal with. One of the key objects is to know whose payment is due today so that you can call them and collect the payment immediately.

Assume below the list of customers you have in the database.

VBA Date Function Example 2-1

We have already written a code that will notify us as soon as we open the Excel file.


Sub Due_Notifier()
  Dim Duedate As Date
  Dim i As Long

  Duedate = Date
  i = 2

For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
  If Duedate = DateSerial(Year(Date), Month(Cells(i, 3).Value), Day(Cells(i, 3).Value)) Then
    MsgBox "Customer Name : " & Cells(i, 1).Value & vbNewLine & "Premium Amount : " & Cells(i, 2).Value
  End If
Next i

End Sub

Please copy the above code and paste it into the VBA module.

VBA Date Function Example 2-2.pn

Now, double-click on the “This Workbook” option.

VBA Date Function Example 2-3

Now, select “Workbook” from the above dropdown.

VBA Date Function Example 2-4

When you select the option “Workbook,” you can see a private macro automatically opens.

VBA Date Function Example 2-5

Here, the macro name says “Workbook_Open ()”; whenever this workbook opens, what you have to do. Whenever this workbook opens, we need to run the macro we have created.

So, here we need to call the macro we have created by its name. For example, in the above code, our macro name is “Due_Notifier.”


Call Due_Notifier
Example 2-6

Now save this workbook and close it.

After closing it, open the workbook and see the magic.

Now, we will open.

Example 2-7

It shows the customer’s name and due amount for the current date.

The “Customer Name” is “Amar,” and the due amount is “20,883”. It is showing this customer name because the due date for Mr. Amar is 15th March 2019, i.e., today.

Now, click on “OK.” It will show other customer names if the due date is on today.

Example 2-8

It shows Mr. Arvind’s name; his due date is also 15th March 2019.

You can easily identify the customer names as soon as you come to the office. One of the big headaches is gone.

Similarly, we have created one more excel macro, to send auto birthday emails from your Outlook.

Example #3

Assume you are in an “Employee Engagement Team,” and you are responsible for sending birthday emails to your employees. Unfortunately, identifying and sending the email to everyone separately is a painful job.

Hello, my dear friend, don’t worry. We have created a macro for you to send the auto birthday emails to your employees.

We have created some data to test. Below is the image of the same.

Example 3

We must update the employee master according to the table’s headings. Below is the code to send the emails.

Please copy the below code and paste it into the module.

Sub Birthday_Wishes()

Dim OutlookApp As Outlook.Application
Dim OutlookMail As Outlook.MailItem
Dim Mydate As Date
Dim i As Long

Set OutlookApp = New Outlook.Application
Mydate = Date
i = 2

For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Set OutlookMail = OutlookApp.CreateItem(olMailItem)

If Mydate = DateSerial(Year(Date), Month(Cells(i, 5).Value), Day(Cells(i, 5).Value)) Then
OutlookMail.To = Cells(i, 7).Value
OutlookMail.CC = Cells(i, 8).Value
OutlookMail.BCC = ""
OutlookMail.Subject = "Happy Birthday - " & Cells(i, 2).Value
OutlookMail.Body = "Dear " & Cells(i, 2).Value & "," & vbNewLine & vbNewLine & _
"We wish you a happy birhday on behalf of the management and we wish all the success in the coming future" & vbNewLine & _
vbNewLine & "Regards," & vbNewLine & "StrIDE Team"

End If
Next i

End Sub

Open the file and run this code when you come to the office. It will automatically send birthday wishes to the respective email IDs.

Note: You should have Outlook configured in your system.

This article is a guide to VBA Date Function. Here, we learn how to use VBA’s Date function and some simple to advanced examples. We also saw one of the projects where we created one macro that sends auto birthday emails from your Outlook. Below are some useful Excel articles related to VBA: –