How do I grab data from an Automatic Updating Cell (=NOW()) and put it into a different cell and not have it update?

[MrTravisL]

I know this is probably not possible, but hey I’ve done some pretty amazing things with Calc so it’s worth asking. What I am trying to do is hard to explain so i’ll just give an example.

Cell A1 is =NOW() - Cell B1 is =A1
So lets say A1 says: December 1, 2016 5:55 AM (the current time), and I put in =A1 to B1 (which would grab the December 1, 2016 5:55 AM and put it into B1).
What I want to happen is, lets say in 10 minutes when NOW() updates to December 1, 2016 6:05 AM, I want B1 to STAY at December 1, 2016 5:55 AM and NOT change to the current time.

Basically what I want to happen is record the time when I put the formula into the cell and not update anymore. I know this could all be avoided by just typing in “December 1, 2016 5:55 AM” but with what I am working on that actually takes quite a bit of time.

[mikekaganski]

It’s worth to understand what is the moment when you want to record the time in B1. What is that special in the second when you have pressed Enter in B1’s formula? I mean, wouldn’t that be better if you made a pushbutton and a macro that would update B1 with static value of current time instead of formula (and possibly refresh A1, if required)?

Also, depending on your workflow, you could use insert special (Shift-Ctrl-V) to paste to B1 after you copied A1. There, you could deselect Formulas

[PYS]

Hi

@Lupp proposed a smart use of circular references here (question was tagged “writer” but it is a Calc solution).

This solution also described in the FAQ.

Regards

[Lupp]

I would emphasise again that the mentioned solution should only be considered in noncritical cases. It will not make any spreadsheet history aware. The information you create and keep this way once spoilt by any event cannot be retrieved. In specific any measure causing the information to change cannot be made undone by Ctrl+Z. You cannot even set it back manually to the previous value if you still know it. Thus the information is not actual data at all.