How can I freeze the answer to a function?

[HomerDPoe]

Here's the question: "How can I freeze the result of a function?"

Here's a made up example:

Example: Suppose I put a function like NOW() or TODAY() at the top of my empty, read-only, purchase request form,
so when it was filled in the date would appear automatically.
So far so good. It had that day's date and I saved it with a different name for future reference.
But when I come back to print a copy, the date would now be the printing date instead of the request date, making it useless as a record.
I would have to look up the date filed, type that into the form and then print it.

So, Here's the question: "How can I freeze the result of a function?"

[dilipandey]

Hi HomerdPOe,

After entering the formula, you can copy - paste special - values either manually or through the help of a quick macro. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[HomerDPoe]

I guess that's something, but it seems like it's more work than just typing the date.
I was hoping for something that would automate the process. Instead of setting a function to =TODAY(), I would enter it as =FREEZE(TODAY())

[dilipandey]

Hi HomerdPoe,

As per me this is not achievable using formulas unless you have some other criteria in your spreadsheet like if Cell A1 = "Yes" then Today() else "any other date" OR may be else Today() - 6 days etc... Alternatively, you can rely on macro as well. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Kmess808]

Hey HomerdPoe, I’m trying to find out the same question. I have a spreadsheet where I log all the current projects everyone is working on and I need to track from when the project was logged to when it was completed. When the project status colum is changed to "complete" or "dead" the completion date colum is automatically populated with the current date which works great, however once the date is entered I don’t want it to ever change again and as it stands right now it updates with the current date when its next open. Here’s my formula =IF(OR(G3="COMPLETE",(G3="DEAD")),TODAY(),"---").

If anyone can help both HomerDPoe and I, it would be vary much appreciated.

[DGagnon]

you would have to use a macro to accomplish that, but if you are just trying to find an easier way to enter the date into a cell you cal press Ctrl+; or for the current time Ctrl+Shift+;

[HomerDPoe]

you would have to use a macro to accomplish that, but if you are just trying to find an easier way to enter the date into a cell you cal press Ctrl+; or for the current time Ctrl+Shift+;

Control-semicolon is handy, all right. Not part of the question, though.

[Pepe Le Mokko]

First go to Tools Options - Check " Iterations" - Then, in your destination cell ( say B1), enter =IF(A1<>"",IF(B1="",NOW(),B1),"")
When A1 is entered B1 will be timestamped.
There are of course VBA solutions to this problem

[HomerDPoe]

Pepe, I like that. It's what I was thinking of. I'll try it. Thanks.

----
Update:
Worked great.