Auto Date in Excel—Here’s What You Need to Know

In Excel, you may find the need to enter dates to make trackers like Gantt Charts, Project Progress, Attendance sheets, Calendars, and so on. 

It’s pretty easy to record dates in Excel. When you type numbers like 5-9 or 5/9, the program instantly identifies and formats them as dates (9-May) in the cell.

But, instead of manually adding them, you can auto-fill the columns with dates in many ways.

For Instance, you could also prompt Excel to automatically insert dates after you fill values in a cell by using the formula. 

Static Vs Dynamic Date—Know the Difference

You can enter two types of Dates in Excel, i.e., Static and Dynamic. 

Before you enter the dates, it’s important to know the differences between them as they can impact your data analysis.

First, Static dates are the values that remain unchanged even after the worksheet recalculation. This means once you enter the date, it will remain the same and intact until you manually change it later. 

On the contrary, Dynamic dates are the values that will automatically update after you open the sheet or recalculate the spreadsheet. 

How to Auto Populate Date in Excel?

Use Keyboard Shortcut

To input the Static dates, there’s a keyboard shortcut. Since the combination key is also easy to remember, you can save yourself a lot of time when doing your work. 

You could insert only a date or a date with a timestamp.

If you’re unfamiliar with the word “timestamp,” it’s just a time in digital record format. For example, 2:00 is a timestamp in 1/5/2024 2:00 date.

Keyboard Shortcut:

Present Date Only: Ctrl + ;

Present Time Only: Ctrl + Shift + ;

Present Date + Time: Ctrl + ; , Space Key, Ctrl + Shift + ;

Use TODAY() and NOW() Function

For users who want to have Dynamic dates in their worksheets, there are TODAY and NOW Functions.

These functions fall under Excel’s “Volatile Functions” like RAND, OFFSET, INDIRECT, etc.

The formula presents a different outcome whenever you refresh the sheet or open it again. So, this is how you’d get a new update date or time in the sheet.

Having said that, you won’t have a new date when your sheet hasn’t refreshed for a long time. 

TODAY() Function

This function returns the present date in Short Date Format in Excel. All you need to do is enter =TODAY() in your cell grid. For example, here, I got 1/5/2024.

Apart from inserting the current date, the TODAY function is also used for other different purposes in Excel.

For example, you could calculate the age, verify whether the date lies in the next nth or last nth days, conditionally format dates, and many more. 

NOW() Function

While the TODAY inserts only a date, the NOW function enters a Date and a Timestamp together.

On a cell, type =NOW() and hit enter. You’ll have a date with time. For example, I got 1/5/2024 14:17. 

If I recalculate my sheet after a minute, I will have a different time which is updated with the system’s present time.

Use Auto-Fill

Excel’s Auto Fill tool is one of my favorite features for data entry as it gets my job done in just two steps. 

All you need to do is enter the first date and extend the Fill Handle. It comes with a smart AI tool that’ll auto-populate your cell with dates. Remember, it’ll insert the Static Dates.

Firstly, type Any date on the cell. Then, hover over the bottom-right corner. Once the + cursor appears, extend the Fill handle down.

Or, in case you need dates horizontally, you can drag the Fill handle towards the right. 

Similarly, you could also extend the Fill handle both right and down.

Once you have the array of dates, you can choose how you want to fill dates. For Instance, Fill Days, Weekdays, Months, Years, Formatting Only, or Without Formatting.

To do that, click on the Auto Fill Options. Then, from the lists, choose any one option. Here, I picked Fill Years. So, I’ll have a series of dates based on Each Year. 

Use SEQUENCE Function

Normally, we use the SEQUENCE function to return an array of sequential numbers. 

Just like for the numbers, this function also allows you to insert an array of dates in a sequence.

Personally, I use this function nested with others to generate Automatic Calendars from scratch in Excel. 

Example 1: 

As an example, let’s generate the dates in 9 rows. I want the date to start from 9/2/2023. Here’s the formula.

=SEQUENCE(9, 1, "9/2/2023", 1)

In the formula, I have specified the SEQUENCE function to return an array of dates starting from 2nd September 2023 in 9 rows and 1 column. Since my step argument is 1, the gap between the two dates would be 1 day.

Likewise, if you need to auto-date horizontally in columns, you could just switch the row and column number in the formula.

=SEQUENCE(1, 9, "9/2/2023", 1)

The formula will return the dates in serial numbers. So, you need to change the number to a Short Date or Long Date format.

However, if the date format is not changing, you might have to troubleshoot it first.

Example 2: 

Next, you can also return the array of dates in a criteria using the SEQUENCE function. 

Suppose, I want to insert only Dates for Friday for the Year 2024. 

Formula:

=SEQUENCE(52, 1, "1/5/2024", 7)

Since there are 52 weeks in a Year, I have specified 52 rows and 1 column to return. Here, my start date is the first Friday of the first month which is “1/5/2024.” Similarly, as I have mentioned 7 numbers, the formula returns the next date in a 1-week gap.

Thus, I’ll only have the Dates for Fridays. You can change the date as you wish in the formula. 

Use IF with TODAY and NOW

So far, we covered how to enter only dates in Excel. But, can you make it more automated and link it with the cells?

For example, as soon as I enter information in a cell, I want to have a present date for that value in the adjacent column. But, I wish to keep it Static and not update it.

Let’s achieve this using the IF with multiple conditions, TODAY/NOW functions, and Does Not Equals to operator (<>).

But, before entering the formula, you need to turn on the Iterative Calculation. 

Step 1: Enable Iterative Calculation

1. From the File tab, head to the Excel Options.
   
2. Click Formulas. 
3. On the Calculation options, tick the box for Enable iterative calculation. Click OK.
   

Step 2: Enter Formula

On a new cell, type this formula. Make sure you enter this next to the data cell which could be on the right or left.

As an example, I will use the Formula 1 in Column B.

Formula 1: Enter Date

=IF(A10<>"", IF(B10<>"", B10, TODAY()),"")

Formula 2: Enter Date and TimeStamp

=IF(A10<>"", IF(B10<>"", B10, NOW()),"")

Use Auto-Fill to copy the formula for the rest of the columns.

While the column is still selected, change the Cell format to Date from the Home Tab. Here, I chose Short Date.

Now, when I enter numbers or text in Cell A10, I’ll get static present date and time in Cell B10. 

Formula Breakdown

• IF(B10<>””, B10, TODAY()): Here, we have passed down the IF function to check whether the B11 cell is not equal to null (B11 <> “”) or not. It returns the value of B11 which is Date when true and TODAY() when false.
• IF(A10<>””, IF(B10<>””, B10, NOW()),””): Similarly, here, we will test (A10<>””) condition. In case the condition is true, it’ll return the value of TODAY. Likewise, when the criteria is false, the formula would result in null (“”).

This is why you have no values in the output cell when column A is empty and present date when Column A has data.

Use VBA

Moving on, if you find the need to regularly enter static dates linked to a cell, we can make a custom function to shorten the process. 

Once you have the Function, you can just use =Function() it to auto-fill dates. It’s a lot quicker, right?

1. Use the Alt + F11 shortcut to open the VBA window. Ensure your Excel has a Developer Tab.
2. Choose Insert > Module.
   
3. Copy-Paste code. Then, click the Save button.
   

Function AutoDate(Reference As Range)
If Reference.Value <> "" Then
AutoDate = Format(Now, "dd-mm-yyyy")
Else
AutoDate = ""
End If
End Function

Now, use the function on your spreadsheet

For example, I entered =AutoDate(D4). Then, when I type iPhone in D4, I’ll have the date in the formula cell (C4).

In case your Excel does not recognize dates, you can check our other article that covers the proven fixes.