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.

=TODAY()

Use TODAY Function to Insert a Current Date in Excel

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

Use TODAY Function to Insert a Current Date in Excel

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.

Use of NOW Function to Insert Date That Will Auto-update

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.

Auto Insert Timestamp Using Excel Options

  • 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()),"")

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

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

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

Formula Breakdown:

  • We used a condition to auto insert date and time. So we used the IF function.
  • IF(B5<>"") implies if there is any data in B5 then it will proceed to the next activity. Otherwise, the cell will not change.
  • IF(C5<>"",C5,NOW()) means if there is any data in 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


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())),"")

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

  • 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 B5<>"", will return IF(AND(C5<>"",CELL("address")=ADDRESS(ROW(B5),COLUMN(B5))),NOW(),IF(CELL("address")<>ADDRESS(ROW(B5),COLUMN(B5)),C5,NOW())) since the condition is true. And it will return an empty string "" 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))) and value_if_true: NOW() and value_if_false:  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:C5<>"", which is 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)) which is FALSE. Why is it so? Because, CELL("address") returns “$C$5” and  ADDRESS(ROW(B5),COLUMN(B5)) returns {“$B$5”}. Hence this condition is not met. So, AND(C5<>"",CELL("address")=ADDRESS(ROW(B5),COLUMN(B5))) becomes AND(TRUE,{FALSE}) and finally, it returns FALSE.
  • Let’s rewrite the entire formula now: =IF(B5<>"",IF(FALSE,NOW(),IF(CELL("address")<>ADDRESS(ROW(B5),COLUMN(B5)),C5,NOW())),""). Now, the IF(CELL("address")<>ADDRESS(ROW(B5),COLUMN(B5)),C5,NOW()) part will come into action.
  • Here, the third IF function has another condition, CELL("address")<>ADDRESS(ROW(B5),COLUMN(B5)). CELL("address") =”$C$5″ and ADDRESS(ROW(B5),COLUMN(B5)) ={“$B$5”}, so the condition is 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 =IF(G5<>"",IF(FALSE,NOW(),{44850.8382697917}),""). Here 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

Employ a VBA Code to Insert and Update Dates

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

Insert Date in Excel That Updates

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

Nasir Muhammad Munim

Nasir Muhammad Munim

I am Nasir Muhammad Munim from Dhaka, Bangladesh. I work as an Excel and VBA Content Developer for Exceldemy right now. Electrical and electronic engineering was my major at Islamic University of Technology, where I got my degree. I love creative works and learning new things. And I'm really into online games, especially first-person shooter games.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo