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

Amanda Merrigan

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 populated...so 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 :)

Paul Newcome

Try this...

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

Amanda Merrigan

Thanks Paul.

I don't want the actual finish date populate in the target finish date. I want the result of the formula (=WORKDAY([Start Date (Target)]33, -1), to be a stand alone date without the formula reference. That way we can track work performance - if actual date is later than target date the task is late...regardless if the production dates change.

If further clarification is required please let me know.

Paul Newcome

Will your Target Start Date change?

Amanda Merrigan

No - it is based on the target finish minus duration (which isn't showing in the screenshot). So if the target date gets "frozen" or becomes a hard date (instead of calculated off the formula) then the start will stay the same. This will be applied to each individual sub-task/child line individually.

The idea is that we often have drawings go out for approval and the consultants do not hit the target date. At this time, the production schedule will be removed, which will produce a #invalid input in the Issued for Production task Finish Target Date...which then trickles all the way up through all the other tasks due to the formula relationships. If the task is complete - we want to retain the date and avoid the #invalid input...or later dates that will be populated once the production schedule (usually later dates) is re-established.

Paul Newcome

SO here is my understanding thus far...

[Finish Date (Target)]35 is pulled from another sheet.

Once that date is pulled, formulas generate the [Start Date (Target)] and [Finish Date (Target)] cells above it.

You want to LOCK these dates in even if [Finish Date (Target)]35 later changes?

Amanda Merrigan

Yes, it is pulled from another sheet.

And yes - I want the generated cells above it locked if the date in the other sheet changes...

BUT - only if the task is actually complete (ie, the Finish Date (Actual) is populated).

Paul Newcome

There currently isn't a way to do this with Smartsheet. A third party tool such as Zapier may be able to accomplish this, but I am not very familiar with that one. Otherwise you would need to manually enter the corresponding date as you enter dates into the [Finish Date (Actual)] column.

Amanda Merrigan

Would there be a way to do this by adding another column so we could at least track the dates even if the ones in the Target Finish Date move? Move the dates as into another column as actual dates instead of the formula?

Paul Newcome

Yes. If you added another column, you could manually enter the dates there as needed and let the formulas in the original columns run.

Amanda Merrigan

Hmmm - I wasn't thinking manually enter them, but a way to automatically bring them in as a date, not referencing the formula.

I guess it's probably the same thing as trying to get them to convert in the target finish date cell....

Paul Newcome

Yes. To accomplish exactly what you are wanting, you will need to use a third party tool such as Zapier.