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 + ;**

**NOTE:**If you get a ##### error, it’s because the date and time is too long to fit in the cell. Expand the column to solve this.

### 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.

**NOTE:**Excel inserts the present time based on your

**system’s clock**. So, if you aren’t getting the date as you expected, change the Region/Time settings.

#### 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.

Function | Syntax | Function Arguments |

SEQUENCE | =SEQUENCE(rows, [columns], [start], [step]) | rows: specify how many rows you want to returncolumns: specify the amount of column you want the formula to returnstart: choose the initial number to start the sequence from[step]: set the amount you want to increase each value |

**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**

- From the File tab, head to the Excel
**Options**. - Click
**Formulas**. - 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**.

**NOTE:**If you used Formula 2 which is the NOW function, apply the

**Date and Time Custom Format**from the Format Cells menu.

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?

- Use

shortcut to open the VBA window. Ensure your Excel has a Developer Tab.**Alt + F11** - Choose
**Insert**>**Module**. - 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.