Forum Discussion
Automatically adding the date/time to a cell when another cell is updated
Welp, as suspected, this doesn't work as intended.
VERY VERY close though.
The reason is because upon saving the file, everything updates again to show the current time.
So to really close this formula down, I think the best way forward is to
- Create a live reference block on the sheet that reflects the current date/time
- THEN update this formula to grab the present VALUE from there.
My other thought would be to have the formula self-destruct upon first use, but that would be VERY unlikely. But an idea for a future version. Like "Convert to value after first use".
Haytham Amairah Tagging you since this might be a good idea to add vs VBA. I see your actively suggesting.
Thanks all.
OK, the problem was that I tried to optimize the formula at some point. This is what works:
=IF(C1<>"",IF(B1="",NOW(),B1),"")
I verified that once the date is set, it does not change after the file is saved and then reopened (I set the display format to yyyy-mm-dd HH:mm:ss to verify that the current value of NOW() is used only when C1 is set to non-blank).
The only time it is updated is when its buddy (C1 in this case) is set to blank and then to non-blank.
I don't understand why you would get negative values from NOW(), that isn't normal. Negating it is not a good idea, because the root cause is somewhere else.