ADD LAST COMPLETE DATE

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

Find more posts tagged with

Formulas

functionality

Automations

IT & Operations

Project Management

Accepted answers

Michael Mulford

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

Looks like this....

Screen Shot 2022-01-23 at 9.02.53 PM.png

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.

Screen Shot 2022-01-23 at 9.13.39 PM.png

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 :)

All comments

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.