Techniques for dynamically recording Excel data entry time

Techniques for dynamically recording Excel data entry time

This can be solved using VBA code, or it can be implemented using a formula. Let's talk about the formula to achieve dynamic recording of Excel data entry time steps:

Click File Options formula, and then select iteration calculation.

Enter the following formula in cell B2:

=if (a2= "", "", IF (CELL ("Contents") =a2,now (), B2))

The cell function returns the information of the cells, and the parameter uses "contents" to get the contents of the last active cell.

The formula means: If A2 is empty, it is displayed as white space, and if the contents of the last active cell are equal to A2, the current time is returned, otherwise the contents of the B2 cell are still equal.

Next, select the B-column data range and format the cells:

H "when" mm "minute" ss "Seconds"

OK, now try it:

As long as you enter data in column A and press ENTER to finish, column B can display the current system time.

There is a flaw in this formula, and if you enter the same content as column A in other areas, the time of column B will also change.

If you do not need to enter content in other areas, use this formula to meet your requirements. If you want to enter data in other areas, try this:

=if (a2= "", "" ", IF ((cell (" contents ") =a2) * (cell (" col ") =1) * (Cell (" Row ") =row ()), now (), B2)

The cell function uses "col" and "row" as arguments to get the column number and line number information of the last active cell.

The formula means: If the A2 is empty, is displayed as blank, if the last active cell's contents are equal to A2, and the last active cell's row number and column number are equal to the row number of the left cell (A2), the current time is returned, otherwise the contents of the B2 cell are still equal.