When making some financial or warehouse forms in Excel, we often need to record the time of entering different batches of data separately. And it can be very time-consuming if we do it manually. So, is there a way to record the data entry time in Excel automatically?
Like the answer to many other questions, choosing a right Excel formula can help you saving a lot time and effort.
1. Before entering the data, creating a column to insert the entry time.
2. Enter this formula: =IF(A2=””,””,IF(B2=””,NOW(),B2)) to B2. It means displaying the entry time of A2 in B2. You can adjust it according to your actual spreadsheet.
3. Use fill handle to apply this formula to the cells of the entire column.
4. Click File at the top-left corner of Excel and choose Options on the sidebar.
5. Switch to Formulas tab in Excel Options window, and check Enable
iteractive calculation under Calculation options. Then hit OK to confirm it.
6. Now you can enter the data in column A. You can see there are different numbers show in column B accordingly, but not in the format of time.
7. Select all the cells in Entry Time column and right click the selected area, choose Format Cells in the menu.
8. Select Custom in the Category of Number tab, and enter the time format: yyyy/mm/dd hh:mm:ss in the textbox under Type. You can check the Sample before hitting OK, and adjust the format till you get satisfied. For example, if you just want the date and don’t need the specific time, you can just delete the hh:mm:ss.
9. Click OK to implement the setting, then the numbers in Entry Time column will be converted to the time format you customized. Whoever or whenever opens the spreadsheet, it’ll be very easy to know when was which data entered.