Forum Discussion
How to insert timestamp (IF NOW functions)
Hello all!
Im having trouble with inserting timestamp, as is:
=IF(A2<>"",IF(B2<>"",B2,NOW()),"")
This formula should put timestamp in B2 if any entry in A2 is given, but I got error in result .
=IF(A2<>"",IF(AND(B2<>"",CELL("address")=ADDRESS(ROW(A2),COLUMN(A2))),NOW(),IF(CELL("address")<>ADDRESS(ROW(A2),COLUMN(A2)),B2,NOW())),"")And this one should update timestamp in B2 when A2 entry is updated, also error returns.
What am I doing wrong, please help.
2 Replies
Both formulas return timestamp, here is result for second one
if only you enable iterative calculations in settings
otherwise it'll be circular reference error. But you shall be quite careful with this setting to avoid side effect, e.g. if another circular reference eventually appears in workbook you may miss it and have wrong result in calculations.
Not sure you what you want to achieve with these formulae. TODAY() will update the timestamp every time you open the workbook or even when you enter something in any other cell.
But anyhow, I copied them in a sheet of my own to see what they ar doing. At first, they just returned the value of cell B2.
Rewrote your formulae to:
=IF(A2<>"",IF(B2<>"",NOW()),"")
and
=IF(A2<>"",IF(AND(B2<>"",CELL("address")=ADDRESS(ROW(A2),COLUMN(A2))),NOW(),IF(CELL("address")<>ADDRESS(ROW(A2),COLUMN(A2)),NOW())),"")
then these returned a timestamp based on NOW(). All I did is delete both references to "B2" in the end of your formulae.
Perhaps this helps.
