Forum Discussion
Automatically adding the date/time to a cell when another cell is updated
This worked... But!
I got a negative value returned and no matter the formatting it would not reflect a date.
When I entered text into the block it returned the following value: -44580.41589
So, further troubleshooting shows when it is NOT a negative number it is the value of the time I entered the data into the block.
So how to modify the formula so it DOES NOT return a negative value could resolve this....
Hunting I will go.
I am very close, thank you. 🙂
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.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.
