Printing cell history value

edited 12/09/19 in Formulas and Functions

I have A1 and B1 which are two different dates (for illustration: A1 is today's date, and the other in the following day).  I have data value in Cell C that is changed daily by another cell linking formula from other sheet.

Then I have cell A2 and B2 -- the formula in these cells is: =IF(today()=ColumnA1,CellC) and =IF(today()=ColumnB1,CellC) respectively  i.e. the formula looks at the date in the A1, for cell A2 and date in B1 for cell B2; and takes the value from CellC if it matches todays date. In cell B2, it will look at the value in CellC for the following day. The formula works ok, except the data in A2 becomes empty the following day (Value in B2 works as intended).

How do I retain the value in cell A2 from being changed/emptied after the date is no longer today i.e. the following days? I can see that the cell history has the previous day's data.

Is there a formula like this:

=IF(today()=ColumnA1,CellC,Use_Cell_History_Last_Value) ?


  • Hello,

    Due to the way that the TODAY() function works, you'd need to manually copy the data from A2 into another cell (that doesn't use a formula) in order to retain it. This is due to the way that the TODAY() function and IF() function works, TODAY() always calculates the current date. If this causes the IF statement to no longer be true, it won't return a value—leaving the cell blank.

  • Thanks for the feedback.

    I wanted to avoid the manual copying of data as the today() function wont retain the existing value. But the cell retains the history value. I would like to access this history value and print out in the cell if TODAY() doesnt equal to the current date. I guess this function (to recall history vaue) is not available yet?

  • Hi all, sent this to FEATURE REQUEST. Please share the love :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!