How to freeze the =today() function once data has

Question

This question already has answers here:

Insert today's date with button, as date, not function? (3 answers)

Closed 4 years ago.

I would like to use the =TODAY() function in a table in excel. However, once data has been entered into that table row, I would like it never to change dates again (effectively capturing the date the row's data was added).

This will be used on every row in the table, so as data is entered into the table down the rows, each date will be captured.

Is this possible, how do I do it?

EDIT - pnuts it is not the same as this question. I do not want to use a button, rather a cell formula.

Answer 1

Gary's Student answer is best for this specific problem, but it is possible to freeze volatile worksheet functions without VBA if you are willing to use circular references. In File\Options\Formulas check Enable iterative calculation Then, assuming that A1 is currently blank, enter

=IF(A1 = 0,TODAY(),A1)

Excel's default format handling doesn't know to format this as date - so you would need to do this separately. More work than Ctrl + ;, but there might be some other use-cases of this trick.

Disclaimer: I explicitly tested that this trick prevents recalculation of Now() rather than Today(). I wasn't willing to wait until midnight to test Today().

On edit: Here is an interesting use-case. Say you have an entire range of blank cells with A1 as the upper left corner cell. While that range is highlighted, enter the formula

=IF(A1 = 0, RAND(), A1)

and hit Ctrl + Enter. Then the entire range fills up with different but fixed random values.

Answer 2

Rather than entering the formula

=TODAY()

touch Ctrl + ;

instead. This will put a "frozen" date in the cell.