Status changes formula

angelapaj

Hi All,

I have a project tracker where we would like to add 3 columns:

1. The column that would catch the first delivery date when the Status column changes to completed. If there are any reworks needed on the design it may change back to in progress and then completed again - we'd like the first delivery date to show us when it was completed for the 1st time.
2. The column that would note the last (most recent) delivery date - exactly like the one above but with the most recent date rather than the 1st one.
3. The column to count how many times the Status column changed to completed.

Is that achievable in Smartsheet at all? If so, could I please have any directions as to how I can achieve this?

Thank you!

Paul Newcome

1) I would insert a checkbox type column (can be hidden after setting up) that has a basic formula in it.

=IF([1st Completed Date]@row <> "", 1)

Then you can set up a Record a Date automation to record the date in the [1st Completed Date] column triggered when the Status column changes to Completed with a condition of the above box NOT being checked.

2) This would be a basic Record A Date automation triggered when the Status changes to Completed.

3) This may be possible but could have some limitations depending on scale. How many rows do you have and how many times (roughly) would a status change back to Completed?

Paul Newcome

1) I would insert a checkbox type column (can be hidden after setting up) that has a basic formula in it.

=IF([1st Completed Date]@row <> "", 1)

Then you can set up a Record a Date automation to record the date in the [1st Completed Date] column triggered when the Status column changes to Completed with a condition of the above box NOT being checked.

2) This would be a basic Record A Date automation triggered when the Status changes to Completed.

3) This may be possible but could have some limitations depending on scale. How many rows do you have and how many times (roughly) would a status change back to Completed?

angelapaj

@Paul Newcome thank you, I will try out the above advice.

As for the number three, it is going to be on the main tracker so we will be adding new rows until we have capacity. In terms of changes, I would say x10 is an absolute maximum.

Paul Newcome

When you say "until we have capacity", are you meaning that you intend to max out the sheet, or what is the maximum number of rows you would anticipate?

angelapaj

@Paul Newcome the sheet will be used for years so we are planning to max it out indeed .

Paul Newcome

In that case, we unfortunately don't have a solution (unless you are able to integrate an archiving system).

The only way to get the counts is to use a copy row automation every time the status changes to "Completed" then use a COUNTIFS looking at the sheet that receives the copied rows.

If you are able to integrate an archiving solution to move old rows off of the main tracker and are willing to manually clean up the copy sheet on a regular basis (delete rows - frequency dependent on use), then we could use the above.

Debbie Sawyer

I agree with Paul, the counting of the number of times the status changes its value is not possible in the core product alone, but we could write you an API program that could do it, I am sure!

Email me if you'd like to know more, or if you have a programmer on site that understands the API side of Smartsheet, ask them to write you an app!

Kind regards

Debbie

debbie.sawyer@smarterbusinessprocesses.com