Making Excel formulas only evaluate once

In some cases where you want to get a static answer from a formula you can do what you want without using a formula.

In your example Excel doesn't know whether TODAY() was meant to mean today's date or the date that it was entered in the spreadsheet.

You can, however insert the current date and/or time in a cell: You can select a cell and press CTRL+; or CTRL+SHIFT+; for current date and current time respectively (for both CTRL+; then SPACE then CTRL+SHIFT+;).

In other cases, I tend to copy and then paste special as value.

On the Tools / Options / Calculation menu set Iterations to one

Set up a new cell, lets say in A1, that is equal to itself plus 1 (yes, it is a circular reference)

Each time you calculate, by pressing key F9, you will see the value in A1 increase by one.

Put your formula inside an IF statement so that the your formula only executes when the A2 is equal to one; and is equal to itself when A1 is not equal to one.

So for example: Lets say your formula is in A2 your formula would be =If(A2=1,Today(),A2)

You can test this for yourself using Excel's Now function formatted to show minutes and seconds.

To reset the New Cell back to one, click in the formula bar and hit enter. (ie - re-enter the formula)

This should work provided you are not using Solver elsewhere in the work book.

Also note, that if you have other circular references in the workbook, Excel will not flag them as such.

The best way to solve this issue that I have discovered is to put your changeable equation in one cell and link a data validation "list" to that cell from a different cell. Hide the cell with the equation in it, use the drop down to select a single value. This avoids typos, speeds entry, and keeps the chosen value static no matter how many times the equation it links to gets updated in the future.

For the date example given, put =today() in cell A1, this will be the cell that has the equation in it. Then select cell B1, and go into the ribbon and select data>data validation> data validation. Select "List" from the drop down menu, and in source put =A1.

Now hide cell A1. When you select cell B1, there will be a drop down arrow on the right side. When you click the drop down arrow, your only choice should be the date displayed in cell A1.

I am using this functionality to aid data entry in log sheets. I have cells with formulas that automatically calculate the values that (being true today) should go into the log, and I have cells with data validation linking to the equation cells that allow the user to choose those automatically calculated values from a very short drop down list. Tomorrow, all the equation cells will change but the logged values will not change until someone purposefully changes them. I have simply hidden the columns with equation cells in them so the user cannot see these.