How To Automatically Insert Date Time In Excel

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.

1. 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.
2. 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.
   
3. To implement this, you should input the formula =IF(A1<>””,IF(B1<>””,B1,NOW()),””) in cell B1.
4. The above formula will first check whether cell A1 is empty or not.
5. If cell A1 is empty, then it will display an empty string in cell B1.
6. 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.
7. The formula IF(B1<>””, B1, NOW()) will check whether cell B1 is empty or not.
8. If cell B1 is empty then it will return the result of the function NOW() which is the current date and time.
9. If cell B1 is not empty, then it will display cell B1‘s original value in it.
10. 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.
11. 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.
12. 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.
13. If you find that column B contains number value only, that is because column B‘s cells display format is not correct.
14. You should right-click the cells in column B, and then click the Format Cells… menu item in the popup menu list.
15. Then change the cell display format in the Format Cells dialog to display date time values correctly.
16. 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.

1. 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.
2. 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.
3. 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.
4. The formula is something long, we will analyze it in the following.
5. 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())
   
   )
   
   ,"")

6. There are 3 nested IF statements in the above formula.
7. The first IF statement checks whether cell A1 is empty or not (A1<>””).
8. If cell A1 is empty then it returns an empty string in cell B1.
9. 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.
10. The second IF statement checks the condition AND(B1<>””,CELL(“address”)=ADDRESS(ROW(A1),COLUMN(A1))).
11. The excel AND function will return TRUE when all the conditions in the AND function are TRUE.
12. 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))).
13. When the AND function returns TRUE, then it will return the NOW() function’s result and display the date & time in cell B1.
14. When the AND function returns FALSE, then it will calculate the third nested IF statement and return the result to cell B1.
15. The third IF statement is IF(CELL(“address”)<>ADDRESS(ROW(A1),COLUMN(A1)),B1,NOW()).
16. It will first check whether the current cell is cell A1 or not.
17. If the current cell is not cell A1 then it returns cell B1‘s value.
18. Else it will return the NOW() function’s result.
19. The function CELL(“address”) will return the current cell’s address such as $A$1.
20. The function ROW(A1) will return cell A1‘s row number such as 1.
21. The function COLUMN(A1) will return cell A1‘s column number such as 1.
22. The function ADDRESS(ROW(A1), COLUMN(A1)) will return the cell address decided by the row and column number such as $A$1.
23. 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.