Often while working in** Excel**, we may need to** insert a date** that can be updated automatically or that inserts automatically while inserting new data. In this article, we will learn how to insert a date in excel that updates automatically.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here.

## 5 Methods to Insert Date in Excel That Updates

There are multiple ways to **insert a date** in Excel that updates. There are Excel-defined functions, custom functions, and many other methods. We will describe them step by step below.

### 1. Use TODAY Function to Insert a Current Date in Excel

Excel has a built-in function that allows us to insert the current date in any cell. The** TODAY function** in excel inserts the current date instantly in any cell of the worksheet. To do so, we will follow these steps.

**📌 Steps:**

- First, we will select the cell where we want to put the current date. In our case, the cell is
**C4**. - Second, we will enter the following formula in the formula bar.

`=TODAY()`

- Third, by pressing
**Enter**, we will see the current date inserted into the cell like the image below.

**Read More:** **How to Insert Day and Date in Excel (3 Ways)**

### 2. Use of NOW Function to Insert Date That Updates Automatically

In Excel, like the **TODAY** function, the** NOW function** can also be used to insert a date that updates and it also inserts time as well. The steps are also exactly similar to the **TODAY** function. After selecting the desired cell, the only change is to write **NOW()** instead of **TODAY()** like the following image.

Again pressing** Enter** will give us the following output.

Here the date is in **mm/dd/yyyy** format and the time is in **24 hr.** format.

**Read More:** **How to Enter Time in Excel (5 Methods)**

### 3. Auto Insert Timestamp Using an Iterative Formula with IF and NOW Functions

If we want that we will insert data that will automatically insert the current date into an adjacent cell, we can use a timestamp. This is also an example of date that updates in Excel. For the method, we will follow these steps.

**📌 Steps:**

- Firstly, we will go to the
**File**tab in the**Ribbon**and click on**Options**.

- Secondly, we will go to
**Formulas**in the**Options**panel and click on**Enable iterative****calculation,**and press**OK**.

- Thirdly, we will select column
**C**and press**Ctrl+1**for the**Format Cells**dialog box to appear. Select**Custom**and write the following in**Type**section and press**OK**.

**m/d/yyyy h:mm AM/PM**

- Then we will select our desired cells and write the following formula and press
**Ctrl+Enter**. In our case, it’s**C5:C11**.

`=IF(B5<>"",IF(C5<>"",C5,NOW()),"")`

- Now, if you write something in cells
**B5**to**B11**, you will see the input time in**column C**.

**Formula Breakdown:**

- We used a condition to auto insert date and time. So we used the
**IF function**. implies if there is any data in`IF(B5<>"")`

**B5**then it will proceed to the next activity. Otherwise, the cell will not change.means if there is any data in`IF(C5<>"",C5,NOW())`

**C5**, it will insert the**NOW function**in**C5**.- Finally, the total formula implies, if there is any data in B5, it will insert the
**NOW**function in**C5**. - The
**C5**data will be generated based on**B5**as we have**enabled iterative calculation**.

**Read More:** **How to Change Dates Automatically Using Formula in Excel**

**Similar Readings**

**How to Insert Date in Footer in Excel (3 Ways)****Insert a Date Picker in Excel (With Step-by-Step Procedure)****How to Auto Populate Date in Excel When Cell Is Updated**

### 4. Insert Current Timestamp Using Formula with IF, CELL, ADDRESS, NOW and COLUMN Functions

If we do not want to enable the iterative calculation, we can use this method that only requires an Excel formula to insert the date that updates. In this method, we will use only one formula with IF, **CELL**, **ADDRESS**, NOW, and **COLUMN** functions. The steps are as below.

**📌 Steps:**

- At first, we will format the data type. So, we will select column
**C**and press**Ctrl+1**on the keyboard. Format Cells dialog box will appear. Then we will go to Custom and write the following format in the**Type**section and press**OK**.

**m/d/yyyy h:mm:ss AM/PM**

- Then we will select our desired cells where we want to insert the date. In our case, we selected
**C5:C11**. - Next, we will write the following formula in the formula bar and press
**Ctrl+Enter**.

`=IF(B5<>"",IF(AND(C5<>"",CELL("address")=ADDRESS(ROW(B5),COLUMN(B5))),NOW(),IF(CELL("address")<>ADDRESS(ROW(B5),COLUMN(B5)),C5,NOW())),"")`

- After that we will see if we insert any data into the mentioned cell in the formula, date and time will automatically be inserted into the formulated cells.

**Formula Breakdown:**

This formula is basically made of three IF functions, one inside another. Let’s see this part by part.

**⦿ First Iteration:**

- When you input something in cell B5, the outer-most IF function which has the logical test
will return`B5<>""`

,since the condition is true. And it will return an empty string`IF(AND(C5<>"",CELL("address")=ADDRESS(ROW(B5),COLUMN(B5))),NOW(),IF(CELL("address")<>ADDRESS(ROW(B5),COLUMN(B5)),C5,NOW()))`

if the condition is not met.`""`

- Now let’s come to the 2nd IF part. Here the outer-most IF function also has 3 arguments, the logical test:
`AND(C5<>"",CELL("address")=ADDRESS(ROW(B5),COLUMN(B5)))`

`NOW()`

`IF(CELL("address")<>ADDRESS(ROW(B5),COLUMN(B5)),C5,NOW())`

. - Initially,
**C5**has nothing in it, so both the conditions in**AND function**are true, and the 2nd**IF**will return the current time,.`NOW()`

Since this is an iterative formula, and the formula is supposed to return each update time, whenever you make a change to **cell C5**, so let’s see how the rest part of the formula works in the next iterations.

**⦿ Next Iterations:**

- Suppose, you have changed the input in
**cell B5**further. - Let’s explain the logical test part here in the 2nd
**IF.**Here the**AND**function has 2 logical tests:which is`C5<>""`

,**TRUE**now (because you already have an update time in**cell C5**from the 1st iteration, it is no anymore empty), and`CELL("address")=ADDRESS(ROW(B5),COLUMN(B5))`

**FALSE.**Why is it so? Because,returns`CELL("address")`

**“$C$5”**and`ADDRESS(ROW(B5),COLUMN(B5))`

**{“$B$5”}**. Hence this condition is not met. So,becomes`AND(C5<>"",CELL("address")=ADDRESS(ROW(B5),COLUMN(B5)))`

**AND(TRUE,{FALSE})**and finally, it returns**FALSE**. - Let’s rewrite the entire formula now:
. Now, the`=IF(B5<>"",IF(FALSE,NOW(),IF(CELL("address")<>ADDRESS(ROW(B5),COLUMN(B5)),C5,NOW())),"")`

part will come into action.`IF(CELL("address")<>ADDRESS(ROW(B5),COLUMN(B5)),C5,NOW())`

- Here, the third IF function has another condition,
and`CELL("address")<>ADDRESS(ROW(B5),COLUMN(B5))`

.`CELL("address")`

=”$C$5″`ADDRESS(ROW(B5),COLUMN(B5))`

={“$B$5”},**TRUE.**So the**IF**function will return what is already in**cell C5**(the first input time). **Cell C5**has the output of the**NOW**function from the 1st iteration. So the return is the time that is already in**cell C5**.- The whole formula now becomes
. Here`=IF(G5<>"",IF(FALSE,NOW(),{44850.8382697917}),"")`

**44850.838**is the recorded time in**cell C5**. - Finally, the formula returns
**{44850.8382697917}**. - The applied custom format
**m/d/yyyy h:mm:ss AM/PM**will make the output understandable:**10/16/2022 8:07:07 PM**.

**Read More:** **Excel Macro: Insert Date and Time in a Cell (4 Examples)**

### 5. Employ a VBA Code to Insert and Update Dates

When we are working with a big calculation or inserting a date that updates is a part of a big program, we can employ VBA code to do so. The steps are below.

**📌 Steps:**

- In the beginning, we will
**Right click**on the sheet we want to apply this VBA method and select**View Code**like the image below.

- Next copy the following code to the new window.

```
Private Sub Worksheet_Change(ByVal tg As Range)
On Error GoTo Handler
If tg.Column = 2 And tg.Value <> "" Then
Application.EnableEvents = False
tg.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss AM/PM")
Application.EnableEvents = True
End If
Handler:
End Sub
```

- Now save the
**Excel**file as**Macro Enabled Excel Workbook**or with the**.xlsm**file extension. - Then here if we give input in our selected range, we can see the date is automatically inserted in the desired range.

**Read More:** **How to Combine Date and Time in One Cell in Excel (4 Methods)**

## Things to Remember

- Changing the data format in methods
**3**&**4**is very important. Otherwise, we will get the current date converted to another data type. - Time would not update if we update the data in any cell. For time to update, we will need to delete data and re-enter it.
- The
**VBA**method will allow us to enter data in any cell of the sheet and give corresponding dates in the horizontal next cell. But other conditional methods are field specific and bounded. - The column width should be wide enough to show the date and time otherwise it will show errors.

## Conclusion

That was all the methods to insert a date in Excel that updates automatically. If you’re still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all of your questions. For any excel-related problems, you can visit our website **ExcelDemy** for solutions.