Formula to Set a field if something blank

Jenn Moffett

Hi, I'm looking for advice on how to track what my original end date was and then what it ended up being at completion. I have a cell called End Date With Stabilization. That adjusts all the time as we push out closing a project. I want to track what it was first set to comparing what it ended up being at project closing when the status changes to "5 - Completed". I created a column called OG End Date but keep getting circular errors when trying to add in a formula to set this if it was blank previously. =IF(ISBLANK([OG End Date]@row), [End Date with Stabilization]@row, [OG End Date]@row).

I was going to create another field to calculate the number of days difference between the OG End date and the End date with Stabilization for projects that are in status 5 - Completed.

I looked at workflows but doesn't look like it will trigger based on it being BLANK then changing to a date in my End Date with Stabilization field then action to populate another field with what's in the End Date with Stabilization.

Also concerned it will continue to override the value as the end date field changes which I don't want. Basically I want to see the cell history for the end date field and then calculate what I was originally planning on completing this work and when I ended up completing it.

Jenn Moffett

This worked like a charm. thank you! Have a great week!

=MIN( COLLECT( {End Dates Range 1},{End Dates Project ID}, [Project ID]@row))

Brian_Richardson

You can use the Baseline functionality to snapshot the original Start and Finish dates. It also creates a Variance column that calculates the difference in days between the baseline finish and the current finish.

You cannot refer to the same column that you're trying to input results into, that's your circular error. If you want to calculate a date you need a new column to look at the other columns and pick up the date that you want.

Automation date capture will trigger and capture the current date into a cell. It will not trigger and then look at a different cell and pick up the value.

If you don't want to use Baseline then you can use a Copy Row automation to snapshot the row's dates over to another sheet, then perform a lookup to that "snapshot" sheet to find the earliest snapshot to compare to your current date. That "lookup" formula would look like =INDEX({OG End Date}, MATCH({Task ID}, [Task ID]@row, 0)) where {Task ID} is referencing some kind of unique identifier for your row in the snapshot sheet.

Jenn Moffett

Thanks Brian for your help. A follow up question for you or others if you don't mind.

I've created a second worksheet to capture changes made to end date field on my current sheet. I have a workflow that is populating the NEW sheet well when changes are made to End Date with Stabilization column. In my original sheet, I have a formula to match the project ID and find the OLDEST End Date with Stabilization date in the NEW sheet and return to the current sheet. Basically a vlook up with a minimum requirement. I'm getting unparseable so need some formula help.

Current sheet: Name: Project Register, Project ID field is where I'm matching. Putting my OG Date field to add the formula.

NEW sheet: Name: End Dates, Project ID field is what we're matching to. Trying to pull End Dates with Stabilization field.

Formula in current sheet is =MIN(COLLECT({End Dates}, {End Dates Range 1}, {End Dates Project ID}, Project ID))

Where end dates range 1 is end dates with stabilization column and End Dates project ID is the Project ID column from the End Dates sheet.

Thanks in advance.

Brian_Richardson

Hi, makes a lot of sense. Your formula has one too many arguments and the Project ID needs to be wrapped in a [] to indicate it's a column name, and appended with @row so that Smartsheet knows you're trying to match the Project ID on the row.

COLLECT is COLLECT( what you want to collect, criteria range 1, criteria, criteria range 2, criteria)

I'm assuming {End Dates Range 1} is the End Dates with Stabilization column. You can name those references, BTW, to make it easier to understand your formulas.

=MIN( COLLECT( {End Dates Range 1},{End Dates Project ID}, [Project ID]@row} ))

Jenn Moffett

This worked like a charm. thank you! Have a great week!

=MIN( COLLECT( {End Dates Range 1},{End Dates Project ID}, [Project ID]@row))