ADD LAST COMPLETE DATE

Rob_PM

I have the following columns that are linked to another smartsheet:

Citrix Status - Network Status - PC Status - Phone Status - Completion Date

When all columns have the "Complete" status, the Completion Date will register the date of last column that changed to "complete" status.

I really appreciate any help!

Rob

Michael Mulford

It should work regardles the number of sites. The automation is applied to the column.

Looks like this....

If you want to do the condition validation using a formula, you could use an auxiliar column.... with a nested IF formula like this:

=IF([Citrix Status]@row = "Complete", IF([Network Status]@row = "Complete", IF([PC Status]@row = "Complete", IF([Phone Status]@row = "Complete", 1, 0), 0), 0), 0)

Convert it to Column formula...

Still at the end you need to use the Date automation function to input the date when the Aux column changes to 1.

Rob_PM

so if I create Date automation function and it will apply to each column does that mean that each site row that gets completed by the columns, the end date will populate in the Competition Date Column for that site row? Like Site1, site 2, site 3 etc?

Rob_PM

Michael

Thank you very much, it worked using the Automation function :)

Paul H

Use the Record a date automated workflow, with conditions that all four must equal completed

https://help.smartsheet.com/articles/2481986-set-the-current-date-with-record-a-date-action

Rob_PM

I cannot use workflow because there are 260 sites. I need to use a formula that applies to the column.

Michael Mulford

It should work regardles the number of sites. The automation is applied to the column.

Looks like this....

If you want to do the condition validation using a formula, you could use an auxiliar column.... with a nested IF formula like this:

=IF([Citrix Status]@row = "Complete", IF([Network Status]@row = "Complete", IF([PC Status]@row = "Complete", IF([Phone Status]@row = "Complete", 1, 0), 0), 0), 0)

Convert it to Column formula...

Still at the end you need to use the Date automation function to input the date when the Aux column changes to 1.

Rob_PM

so if I create Date automation function and it will apply to each column does that mean that each site row that gets completed by the columns, the end date will populate in the Competition Date Column for that site row? Like Site1, site 2, site 3 etc?

Rob_PM

Michael

Thank you very much, it worked using the Automation function :)

Rob_PM

Michael

I followed the automation process

it only added the date in "Complete Date" when I changed one of the columns to "complete" manually but the other row that was linked to another smartsheet when changed to "Complete" from the other sheet, updated this one "Communication" sheet it didn't register the date in "Completion Date"

Rob_PM

Michael

I did another test and the column is populated with date when the other columns are changed manually. When the column changed linked to another sheet, the Completion Date will not populate the dates.