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.