How to Insert Date in Excel That Updates (5 Easy Methods)

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.

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.

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

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.

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

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

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
• Make an Alternative to Datepicker in Excel (2 Easy Methods)

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.

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

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

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.

Related Articles

• How to Insert Last Saved Date in Excel (4 Examples)
• Formula for Weekly Dates in Excel (5 Examples)
• How to Display Day of Week from Date in Excel (8 Ways)
• Excel Automatically Enter Date When Data Entered (7 Easy Methods)
• How to Insert Drop Down Calendar in Excel (With Quick Steps)
• Insert Day and Date in Excel (3 Ways)