VBA Date Function

VBA Date is a Date and Time function. The VBA Date function returns the current date according to your system date. Also, an important pointto note is that this function has no arguments whatsoever. Thus, we use the DATE function to return the current system date. 

If you are a frequent user of Excel worksheets, you must be aware of a function called "TODAY ()," which will return the current date as per the system date.The DATE function is used in VBA code to find the current system date and  works similar to the worksheet function “TODAY.” However, TODAY() can be entered directly in a cell and recalculates every time the worksheet updates..

The syntax of the Excel DATE function is very simple because it has no arguments and includes only empty parenthesis. 

Date ()

It can be written in VBA code as follows with no parentheses. For example, 

myDate = Date.

The VBA Date function is useful when we must insert today's date, compare dates, or log timestamps in our macros.

Example #1

Assume we must insert the current date in cell A1. Just follow the below steps to write the VBA code to insert the current date in cell A1 using the VBA Date function..

Step 1: Create a macro and name it. Here, we name it as Date_Example1.

Code:

Sub Date_Example1()

Step 2: Since we need to store the current date in cell A1 our code is as follows:

Code:

Sub Date_Example1()

Range("A1").Value

End Sub

Step 3: In cell A1 we want the current date, so let us assign the Date function, as shown below.

Code:

Sub Date_Example1()

Range("A1").Value = Date

End Sub

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

So, when writing the code, the current date in the system was "15th March 2019."

Note: The format of your date depends on your Windows settings. Example #2

Example #2

Assume you are an insurance agent and have several customers to deal with. One of the key objectives is to know whose payment is due on that particualr day so that you can collect the payment from them immediately.

Below is the list of customers which are  in the database.

Step 1: We have written a code that will notify us about the dates as soon as we open the Excel file.

Sub Due_Notifier()

  Dim Duedate As Date

  Dim i As Long

  Duedate = Date

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

Explanation:

For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row

• This line loops through all rows starting from row 2.

If Duedate = DateSerial(Year(Date), Month(Cells(i, 3).Value), Day(Cells(i, 3).Value))

• This line compares today’s date with the date in Column C of each row.

MsgBox "Customer Name : " & Cells(i, 1).Value & vbNewLine & "Premium Amount : " & Cells(i, 2).Value

• If the date matches, a message box pops up showing the customer name from the first column and the amount in the second column. 

Step 2: Copy the above code and paste it into the VBA module.

Step 3: Now, double-click on the "This Workbook" option.

Step 4: Select "Workbook" from the above dropdown.

Step 5: When you select the option "Workbook," you can see a macro automatically open.

Step 6: Here, the macro name says "Workbook_Open ()"; Whenever this workbook opens, we must run the macro we have created.

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

Code:

Call Due_Notifier

Step 7: Now, save this workbook and close it.

After closing it, open the workbook and see how easily it works!.

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., the day on which this code was executed

Step 8: Now, click on "OK." It will show other customer names if the due date is on today.

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

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 strenuous job.

Hence, we have created a macro to send auto birthday emails to all the employees on their birthday.

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

Step 1: We have written 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 birthday on behalf of the management and we wish you all the success in the coming future" & vbNewLine & _

vbNewLine & "Regards," & vbNewLine & "StrIDE Team"

OutlookMail.Display

OutlookMail.Send

End If

Next i

End Sub

Explanation:

If Mydate = DateSerial(Year(Date), Month(Cells(i, 5).Value), Day(Cells(i, 5).Value))

This reads the birthday from column E (5) and checks if it is today’s date.

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 birthday on behalf of the management and we wish you all the success in the coming future" & vbNewLine & _

vbNewLine & "Regards," & vbNewLine & "StrIDE Team"

If true, it sends an email to the address in column G (7) with optional CC from column H (8).

Uses Outlook to send the email with a preset subject and message.

Step 2: Open the file and run this code each day you enter your office.. It will automatically send birthday wishes to the respective email IDs.

Note: You should have Outlook configured in your system.