Freeze =TODAY

Occasional Contributor

Is it possible to freeze the result of =TODAY, so it doesn't update the date everytime I open the sheet?

 

I need the =TODAY function to stay fixed.

6 Replies
You can use a cell to "activate" the function like in this example: https://techcommunity.microsoft.com/t5/excel/freeze-or-stop-formula/m-p/1719595

Example:
If you add a cell AB4 where you add some text "STOP" if you don't want the formula to be calculated.

=IF(NOT(ISBLANK(AB4)),"",DATEDIF(AA4,TODAY(),"D"))

Hope this helps.

@paolive 

 

How do I apply it to my example? I tried to break the formula (presented in your example) down but I just don't get it.

 

PAtesting_0-1646658217671.png

 

Column C has the =TODAY formula I am trying to freeze, because it is essential that the days-prior-or-after-the-deadline information in column D does not change.. 

@PAtesting I was thinking and maybe what you need is something simpler. You know that if you press F9 inside a formula, for example double click in cell C2 and press F9, it will convert to the value? This way you just need to press F9 inside that cell to "freeze" the function.

 

 

 

Maybe Ctrl+; is plausible? it types the date of today.
In your example I, using shortcuts, would
select C3
type Ctrl+Space to select the data column
type Ctrl+; to type the date
type Ctrl+Enter to enter the same value into the selected cells
Thing is, this Excel sheet is fully automated. It extracts information from incoming mails and it gets put in the table, hence the reason I need the formula to freeze instead of me constantly coming back to type the Ctrl code.

I fear it may be a lot complicated than I thought
Mm... then the static value of TODAY should be best as a step in the automation. But... that means that a person with possibility to change it does it.
Wether it is a macro or a power query that fetches the emails, it is possible to do.
If it is an excel plug in, it's harder.
It is probably possible to append a macro that automatically exectues after the automated process, and freezes the TODAY value. But include it in the current automation would be my default suggestion.