VBA Date Functions

In this tutorial, we are going to go through the different built-in VBA Date Functions.

VBA Date Function

You can use the Date Function to return the current date.

The syntax of the Date Function is Date(). It has no arguments.

The following code shows you how to use the Date Function:

Using the Date Function in VBA

VBA Now Function

You can use the Now Function to return the current date and time.

The syntax of the Now Function is Now(). It has no arguments.

The following code shows you how to use the Now Function:

VBA Time Function

You can use the Time Function to return the current time.

The syntax of the Time Function is Time(). It has no arguments.

The following code shows you how to use the Time Function:

VBA DateAdd Function

You can use the DateAdd Function to add a date/time interval to a date or time, and the function will return the resulting date/time.

The syntax of the DateAdd Function is:

DateAdd(Interval, Number, Date) where:

Interval – A string that specifies the type of interval to use. The interval can be one of the following values:

Number – The number of intervals that you want to add to the original date/time.
Date – The original date/time.

Note: When using dates in your code you have to surround them with # or quotation marks.

The following code shows how to use the DateAdd Function:

VBA DateDiff Function

You can use the DateDiff Function in order to get the difference between two dates, based on a specified time interval.

The syntax of the DateDiff Function is:

DateDiff(Interval, Date1, Date2, [Firstdayofweek], [Firstweekofyear]) where:

Interval – A string that specifies the type of interval to use. The interval can be one of the following values:

Date1 – A date value representing the earlier date.
Date2 – A date value representing the later date.
Firstdayofweek (Optional) – A constant that specifies the weekday that the function should use as the first day of the week. If blank Sunday is used as the first day of the week. Firstdayofweek can be one of the following values:

-vbSunday – uses Sunday as the first day of the week.
-vbMonday – uses Monday as the first day of the week.
-vbTuesday – uses Tuesday as the first day of the week.
-vbWednesday – uses Wednesday as the first day of the week.
-vbThursday – uses Thursday as the first day of the week.
-vbFriday – uses Friday as the first day of the week.
-vbSaturday – uses Saturday as the first day of the week.
-vbUseSystemDayOfTheWeek – uses the first day of the week that is specified by your system’s settings.

Firstweekofyear (Optional) – A constant that specifies the first week of the year. If blank then the Jan 1st week is used as the first week of the year. Firstweekofyear can be one of the following values:

-vbFirstJan1 – uses the week containing Jan 1st.
-vbFirstFourDays – uses the first week that contains at least four days in the new year.
-vbFirstFullWeek – uses the first full week of the year.
-vbSystem – uses the first week of the year as specified by your system settings.

VBA Today

Today means the current date, in worksheet Now function does the same thing which gives us the current date and time but there is no inbuilt today function in VBA itself, the method to get the current date of the system is by using the date function and unlike the now function date function only gives us the current date.

In excel, we have several useful functions which can help us on a daily basis. Excel мейд all of our life easy at the workplace. When I say “daily,” excel has the formula to return today’s date as well, not only date, we can also get the current date and time together. Such is the variety of excel formulas. If you are a regular user of excel, I hope you have come across the formula called “TODAY” in excel to insert the current date as showing in your working computer. But we don’t have TODAY function in VBA, then how do we get the TODAY’s date from VBA. This article will show you how to work with the TODAY date in VBA. Read on.

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

What is the Formula to get Today’s Date in VBA?

If there is no formula called TODAY, then how do we get today’s date from VBA? This is the common question everybody asks, but the solution is simple we have a formula with a different name, i.e., DATE function.

What Date Function Does in VBA?

DATE is exactly the same as the VBA TODAY function, but this is not a volatile function unless you run the macro or trigger the macro.

Интересно почитать: Excel не открывает файлы

The syntax of the DATE function doesn’t have any arguments. We just need to pass the function DATE. That’s all.

Examples of Date Function in VBA

DATE function to return the current date of the system. This is very useful when we use this as part of the big VBA project. To have a fair knowledge about this first, I will show you simple examples of DATE function.

Example #1

Step 1: Create a subprocedure by naming the macro.

Step 2: Declare the variable as “Date.” DATE function returns the result as date only, so the variable data type should be “Date.”

Code:

Step 3: Assign the value to variable “k” as DATE function.

Code:

Step 4: Now, the value of the variable “k” in the message box in VBA.

Code:

Run the code. We should see the current date showing in the system.

Note: Date format could vary based on the system settings. It could be in “mm-dd-yy,” “dd-mm-yy.”

Date Function to find the Due is Today.

The date function is more helpful in the context of finding the due dates of EMI, Credit Card payment, Insurance Payments, etc.…

Assume you are working as a loan recovery officer, and you have a list of customers with their due amount and due date.

In the status column, you need the result as “Due is Today” if the due date is equal to the current system date.

Code:

This will arrive at the results in the status column.

Like this in many scenarios, we can use the DATE function to check the dates and perform some kind of action.

You can download this VBA Today Function here. VBA Today Function Excel Template

Insert Date Time Stamp with VBA

Inserting date time in Excel using VBA is as simple as writing a formula in a Cell.

The VBA timestamp code is:

This code will show the current date and time in cell A1. And yeah, that is it. it’s done.

The “Now” function returns current date and time, and “Format” function formats the output into «mm/dd/yyyy HH:mm:ss» format.

Show date and time whenever a change is мейд using VBA.

Let’s say you have prepared a sheet. In this sheet, you want time to be shown in column B whenever someone does an entry Column A.

Now we need a VBA timestamp code that runs every time when change is мейд in column A.

The below code gets the work done easily.

Run Macro When A Change is Мейд (Worksheet Change Event Handler)

To run this code, open VBA and double click on the sheet in the project window on which you want to show timestamp.

Now we have to use VBA event handler since we want to run our code to run every time when a change is мейд.

From the drop-down above your code, select Worksheet.

Now adjacent to worksheet drop down, you see another drop down. It contains a lot of event handlers. For now, select “change”.

Now copy the above code here.

And it is done. Get back to your sheet and check it.

Code Explanation:

The code is written on sheet1 by double-clicking on it. It means your code belongs to only this sheet.
We used “Change” event handler of “Worksheet” to trigger our VBA Code.

The first line ignores error pop and runs these two lines. If you omit On Error Resume Next you will see error popping up when you delete a value in column A.

:- This line checks if a cell in column A is blank. If yes then

:- This line deletes adjacent cell value.

:- This line enters the current time and date in adjacent of A column.

And the last three lines just close their respective block. And it’s done. Now you have a sheet that gives an exact time of change мейд in a cell of column A.

You may have noticed that there are several event handlers in the drop-down list. Play around them. Try to figure out what each event handler works. And if have any difficulty, write it down in the comments section.

Comments

Code enters the date in column f based on any changes мейд in a,b,c,d,e. Only issues I am having is column e has comments that get deleted after adjustments are мейд to the rest, but the date deletes too, even though the other columns have been adjusted. Is there a way to stop the date deleting?

Hi, this works if you enter 1 by 1, meaning the date&time will appear when you enter singular. But what if i have a group of say 6 in a column B and i copy them simultaneously in column A — then i don’t see the date&time next to them (though Date&Time should be equal for all of them). Can you help?

I want the date to insert into C2, but when I change the range to «C:C» it doesn’t insert anything. How would I do this, using the same code above?

Hi Anna,
Make sure that you are writing the code in the sheet you want to have the stamp. Do not use a global module.
If it doesn’t help, let me see your code.

If you wish to have the date in C2, pls change Target Offset as «Target.Offset(0,2)».
The range where you changing as C:C is for the Data source upon changing which the result would give you in Date & Time format. Hope it helps you.

This code is wonderful. However, I am looking to create a sign-in sheet that not creates a time when a customer signs in, but it will create a current time when the clerk enters their name into the sheet and when creates a current time when the clerk customer signs out. I guess I’m having trouble duplicating the code for different columns. Help.

Hi Kim,
As i can understand that you want to a time stamp when clerk writes his name a column and again in a different column when he signs out. Right?
So let’s say your clerks enter there name in column A and you want the time of name entered in Column B. You want to enter time in column D when clerk signs out in colum c, for that use this code.
Actually, we are using worksheet change event here. And you can only have one event of one type on one sheet. So you need to edit the code inside the same event. Use the below code.

You can read about the events in Excel here.
https://www.exceltip.com/events-in-vba/the-worksheet-events-in-excel-vba.html

Thanks for this wonderful code. Would be great help if you can share the VBA code for below mentioned requirement .
Using this code if we insert anything in column A2 or A3 or A4 then in column B2,B3,B4 date and time will be inserted automatically. At the same time if i put anything in column «N» then column «O» must filled the date and time automatically (Doing this i want to capture the start time and end time)

I am using this code successfully, however I would like to advance one step further. When a cell value changes, I would like vba code to insert a cell comment with the date/time the cell value changed. this will eliminate a column in my spreadsheet. Can you help?

So, cute, I download the example, and now I have password protected modules in my VBA. care to share to get these off?

VBA Date

By Dheeraj Vaidya

Excel VBA Date

There are some functions which are really very handy, and we choke our life without those functions being a VBA user. DATE function is one of those functions which can be really very useful at times and can make life easier for a programmer. In an Excel spreadsheet, there is a function called TODAY() which gives the current date as a result based on the system date. On similar lines, VBA has DATE function which gives current date based on the system date.

The VBA DATE function returns the current date based on system date as a result and has really very simple syntax.

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

This function does not have any argument to be passed comes with the name of the function and empty parentheses. It is not mandatory to add the parentheses as well while calling this function. Isn’t this function really simple in nature?

The syntax of DATE function in VBA.

How to Use Excel VBA Date Function?

We will learn how to use a VBA Date function with few examples in excel.

VBA Date Function – Example #1

Suppose, you wanted to see the current date in MsgBox. How can you do that? Just follow the steps below and you’ll be through.

Step 1: Insert a new module in your Visual Basic Editor.

Step 2: Define a sub-procedure to write create and save a macro.

Code:

Step 3: Define a variable named CurrDate which can hold the value of the current date. Since, we are about to assign a date value to the variable, make sure you are defining it as a date.

Code:

Step 4: Using the assignment operator, assign a value of the current system date to the variable newly created. You just need to add DATE in order to assign the date value. Use the following piece of code:

Code:

Step 5: Use MsgBox to be able to see the current system date under Message Box prompt. Use the line of code given below:

Code:

Step 6: Hit F5 or run button manually to Run this code. You’ll be able to see a Message Box as shown in below screenshot with the current date.

Note that, the date shown here in the screenshot is the date I have run this script at. You may be getting a different date at the time you run this code, based on your system date.

This is the simplest example of getting the current date. You can also use Cells.Value function to get the date value in a particular cell of your excel sheet.

VBA Date Function – Example #2

Home Loan EMI payment due date

Suppose I have a worksheet and I need a system to show me a message “Hey! You need to pay your EMI today.” Every time I open my sheet and the value in cell A1 is the current system date. Let’s see step by step how we can do that.

Step 1: Insert a new module and define a new sub-procedure named auto_open() to create a macro. auto_open() allows your macro to run automatically every time you open the worksheet.

Code:

Step 2: Use If condition to assign the value of the current date in cell A1 of worksheet HomeLoan_EMI.

Code:

Step 3: Now, use Then on the same line after IF so that we can add a statement which will execute as long as if-condition is true.

Code:

Step 4: Add a statement to be executed for the condition which is true.

Code:

This statement will pop-up under Message Box as soon as the If a condition is true.

Step 5: As we know, every IF condition always needed an Else condition. Add an Else condition to this loop.

Code:

This else condition will terminate the automatic opening of Macro if a date in cell A1 is not the current system date.

Step 6: Finally, End the IF loop by using statement End IF.

Code:

Step 7: This is it, now every time you open your worksheet the system will automatically run the above code and see if the date value in cell A1 is your EMI due date or not. If the EMI due date equals to the system date, it will show the message as below:

VBA Date Function – Example #3

VBA Date to Find out the Credit Card Bill Payee

Suppose I have a list of customers who have a credit card and you want to know who has payment due today. So that you can call them and ask them to pay their due immediately by EOD.

VBA Date could be handy in allowing you to automate the things instead of checking the dates one by one. Let’s see how to do this step by step:

Step 1: Define a New macro using a sub-procedure under a module.

Code:

Step 2: Define two new variables one of which will be useful in looping the code up and another one in order to hold the value of the current system date.

Code:

Step 3: Now use the following piece of code which helps in searching the person who has a credit card bill due date as the current system date. This code allows checking the Customer who has bill payment due on the current system date along with the bill amount.

Code:

Step 4: Run this code by hitting F5 or Run button manually and see the output.

On the first iteration, we can see that Mohan is the one who has Bill of 12,900 due on 29-Apr-2019 (Current system date on which this code is run). If we hit OK, we can see the next customer name who has a bill due on 29-Apr-2019 (Rajani is the next).

This code will really be handy when you are having millions of rows of customers who have their bill due on one particular day. Please note that all the scripts mentioned in this article are run on 29-Apr-2019. You might get different date value when you run this sample codes based on the system date.

Things to Remember

VBA DATE function returns the current system date and as parallel to Excel’s TODAY() function.
VBA DATE function does not have any argument to be passed in excel. Doesn’t even need the parentheses to be called while using this function in the code.
VBA DATE is a non-volatile function in excel.
VBA stores Date values as DATE at the time of execution. So, does not define a variable holding value as a String/Integer. It will cause an error during execution of the code.