formula to stop a countdown to a future date based on an actual return date

Josh Stoner

Smartsheet community: I currently have three columns: the expected return date(which is a future date), a countdown column to determine how close it is to the expected return date or if it is past due, and the actual return date.

the formula for the overdue column is =[Expected Return Date]@row-Today()

What I am trying to accomplish is the following: if a date is entered into the actual return date column then stop counting, if the cell is blank, then continue counting. The actual return date column is using the date column type.

Would I need to use multiple if statements? Is it even possible to do what I am trying to accomplish? I'm not even sure where, to begin with, this one as I am by no means a guru with formulas.

Paul Newcome

Try this:

=IF([Actual Return Date]@row <> "", [Actual Return Date]@row, TODAY()) - [Expected Return Date]@row

Josh Stoner

Hi @Paul Newcome

I tested the formula and it came back with the number of days between the actual and the expected.

After looking at the formula, I made the following adjustment and it worked:

=IF([Actual Return Date]@row<>"","",[Expected Return Date]@row-TODAY())

I would not have been able to do it without the guidance or help. I truly appreciate it. Thank you!