 # Stop recalculating formula past a set date?

I have a summary file that links to each of my project files, respectively. I update the project files weekly. My summary file is set up with column A-C being my project info, Row 2 is my dates, and columns D and beyond are set dates- weekly.  (Photo below for reference)

How can I set this summary file up so that:

IF the date in Row 2 on the summary file matches the date on the project file, it will update my numbers.

But if the project file is updated, that summary file column does not update- it keeps its value and the next column with the matching date updates to the current info.

Basically I want to freeze, archive, or deactivate the formula once that date has passed so I can track the numbers across a quarter. 5 Replies

# Re: Stop recalculating formula past a set date?

Hi @allimo33,

you can do this with a simple IFS() function in conjunction with TODAY().

``R4 = IFS(TODAY() = R\$2, ValuefromSummary, TODAY() > R\$2, R4, TRUE, "") ``

ValuefromSummary stands for the cell that contains your data for this date.

For this formula to refer to itself, iteration must be switched on in the Excel options.

I hope this helps you.

# Re: Stop recalculating formula past a set date?

Here's how I edited your formula. When I enter this, it gives me 0.00

=IFS(TODAY() = R\$2, '[4475_Program Status.xlsx]Summary'!\$A\$47, TODAY()>R\$2, R11, TRUE, "")

I am trying to match the date in row 2 to the date in a separate file. Like "If this date in R2 is after the date in '[4475_Program Status.xlsx]CV'!\$B\$1, then it does not update and stays the same value as it was. # Re: Stop recalculating formula past a set date?

Hi @allimo33,

the date in line 2 was never less than today's date. Therefore, the value was never drawn from '[4475_Program Status.xlsx]Summary'!\$A\$47. Please change the date in line 2 once to a value higher than TODAY(). And then set it back again. Then it should work.

# Re: Stop recalculating formula past a set date?

Now that removes the zero- I have attached Example Files of what I work with. Blue cells on each are the dates that need to match up, pink are the cells where the \$ value needs to populate once, then freeze (based on dates?) I added comments to these cells as well explaining what I need..... if it's even possible!

# Re: Stop recalculating formula past a set date?

``=IF(D\$2=[Excel Example_Reference File.xlsx]CV'!\$B\$1,[Excel Example_Reference File.xlsx]Summary'!\$A\$47,D\$5)``
``=IFS(D\$4="reset","",D\$2>[Excel Example_Reference File.xlsx]CV'!\$B\$1,"",D\$2=[Excel Example_Reference File.xlsx]CV'!\$B\$1,[Excel Example_Reference File.xlsx]Summary'!\$A\$47,TRUE,D5)``

In the second formula, no old values are taken along when the cell is copied. You can also reset the entry by entering "reset" in line 4 above the formula.