In the previous article, I told you how to insert the date & time in excel using excel functions or shortcuts manually. In this article, I will tell you how to insert the date & time in one excel cell automatically when you input or update data in another excel cell.

### 1. How To Insert Date & Time In Excel Automatically Using Formula.

- There are 2 columns in this example, the first column (column
**A**) is the**ID**column, and the second column (column**B**) is the**TIME**column. - After you input an id in the first column cell, it will automatically insert the current date & time in the second column after the id cell.

**A****B****1**1 1/19/23 10:06:09 AM **2**2 Â 1/19/23 10:06:26 AM **3**3 Â 1/19/23 10:06:47 AM - To implement this, you should input the formula
**=IF(A1<>””,IF(B1<>””,B1,NOW()),””)**in cell**B1**. - The above formula will first check whether cell
**A1**is empty or not. - If cell
**A1**is empty, then it will display an empty string in cell**B1**. - If cell
**A1**is not empty (after you input an id value), then it will display the result of the formula**IF(B1<>””, B1, NOW())**in cell**B1**. - The formulaÂ
**IF(B1<>””, B1, NOW())**will check whether cell**B1**is empty or not. - If cell
**B1**is empty then it will return the result of the function**NOW()**which is the current date and time. - If cell
**B1**is not empty, then it will display cell**B1**‘s original value in it. - Flash fill the formula from cell
**B1**to**B3**, you can refer to the article Flash Fill In Excel & How To Autofill Formula In Excel. - Then it will insert the formulaÂ
**=IF(A2<>””,IF(B2<>””,B2,NOW()),””)**in cell**B2,**andÂ**=IF(A3<>””,IF(B3<>””,B3,NOW()),””)**in cell**B3**. - If you meet an excel circular reference error, you can read the article How To Fix Excel Circular Reference Error There Are One Or More Circular References Where A Formula Refers To Its Own Cell Either Directly Or Indirectly to learn how to fix it.
- If you find that column
**B**contains number value only, that is because column**B**‘s cells display format is not correct. - You should right-click the cells in column
**B**, and then click the**Format Cells…**menu item in the popup menu list. - Then change the cell display format in the
**Format Cells**dialog to display date time values correctly. - You can read section
**1.2 Change Date & Time Display Format In Excel Cell**Â in the article How To Insert Date & Time In Excel With Excel Functions Or Shortcuts to learn more.

### 2. How To Update Date & Time In Excel Automatically When Update Data In Column A.

- When you update column
**A**‘s data in the above example, you will find the date & time value in column**B**will not change automatically. - Now, I will tell you how to change the date & time value in column
**B**automatically when you update the**ID**value in column**A**. - To implement this, you should input the formula
**=IF(A1<>””,IF(AND(B1<>””,CELL(“address”)=ADDRESS(ROW(A1),COLUMN(A1))),NOW(),IF(CELL(“address”)<>ADDRESS(ROW(A1),COLUMN(A1)),B1,NOW())),””)**in cell**B1**, and then flash fill the formula in cell range**B1:B3**. - The formula is something long, we will analyze it in the following.
- We can separate the long formula into the below parts.
=IF(A1<>"", IF( AND(B1<>"",CELL("address")=ADDRESS(ROW(A1),COLUMN(A1))), NOW(), IF(CELL("address")<>ADDRESS(ROW(A1),COLUMN(A1)),B1,NOW()) ) ,"")

- There are 3 nested
**IF**statements in the above formula. - The
**first IF**statement checks whether cell**A1**is empty or not (**A1<>””**). - If cell
**A1**is empty then it returns an empty string in cell**B1**. - If cell
**A1**is not empty (when the user inputs or updates the id value in**A1**), then it will calculate the second**IF**statement. - The
**second IF**statement checks the condition**AND(B1<>””,CELL(“address”)=ADDRESS(ROW(A1),COLUMN(A1)))**. - The excel
**AND**function will return**TRUE**when all the conditions in the**AND**function are**TRUE**. - For example, the above
**AND**function will return**TRUE**when cell**B1**is not empty (**B1<>””**) and the current cell is cell**A1**(**CELL(“address”)=ADDRESS(ROW(A1), COLUMN(A1))**). - When the
**AND**function returns**TRUE**, then it will return the**NOW()**function’s result and display the date & time in cell**B1**. - When the
**AND**function returns**FALSE**, then it will calculate the third nested**IF**statement and return the result to cell**B1**. - The
**third IF**statement is**IF(CELL(“address”)<>ADDRESS(ROW(A1),COLUMN(A1)),B1,NOW())**. - It will first check whether the current cell is cell
**A1**or not. - If the current cell is not cell
**A1**then it returns cell**B1**‘s value. - Else it will return the
**NOW()**function’s result. - The function
**CELL(“address”)**will return the current cell’s address such as**$A$1**. - The function
**ROW(A1)**will return cell**A1**‘s row number such as 1. - The function
**COLUMN(A1)**will return cell**A1**‘s column number such as 1. - The function
**ADDRESS(ROW(A1), COLUMN(A1))**will return the cell address decided by the row and column number such as**$A$1**. - Now when you input or update the cell value in column
**A**, the date & time value in column**B**will change to the current date and time automatically.