Can I return the hard value of the result of a formula based on another formula being populated?

Amanda Merrigan
Amanda Merrigan ✭✭✭✭✭
edited 01/17/20 in Formulas and Functions

I have formulas used to create target finish dates.

What I would like is to have the target finish date show the date based on the formula when the Actual finish date column is blank. But when the actual finish date is populated I would like the formula to be removed and the date that the formula produced be that if anything in the cell it was referencing changed, that date doesn't change.

Below is an example...

The Issue for Production Finish Target Date is linked in from our Operations Schedule. This date is the driver to backwards schedule our design tasks. We currently have a formula setup to calculate that the finish date of a task is the start date of the next task minus 1. The start dates are finish date minus duration and shouldn't be affected by this. What I would like is if, when the actual finish date is populated for the "Submit" task, the January 31 target finish would remain in tact, even if the Issued for Production Finish date (Operations date) changed. We want to use this to track performance.

I was hoping I could do an if statement similar to this, but I'm falling short on the "if false" part of it.

=IF([Finish Date (Actual)]32 = "", WORKDAY([Start Date (Target)]33, -1), DATE())

Any advice would be appreciated :)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!