NEED TO ADD LAST COMPLETION DATE IN COMPLETION DATE COLUMN

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 row that changed to "complete" status.

NOTE: The Record a date automated workflow only registers for manual update. When the column is updated via linked to another smartsheet that updates the "complete" won't work using the date automation. I already tested many times and reported as a bug.

Below is the example that Automated workflow didn't work when is updated via link:

The columns that I have are: Location, Citrix Status, Network Status, PC Status, Phone Status all updated liked to another smartsheet and final the Completion date.

I need a formula something like this:

=IF([Citrix Status]@row = "Complete", IF([Network Status]@row = "Complete", IF([PC Status]@row = "Complete", IF([Phone Status]@row = "Complete", .........something to register the date BUT the date in Completion Date cannot change every time I open the smartsheet. Also, if the formula already registered a date but one of the columns changes from "Complete" to something else, the date from "Completion Date" has to disappear. The formula needs to be converted to column formula so every row can have the "Completion Date" registered.

I really appreciate all your help very much since I am stuck on this.

Thank you so much!🙏

Rob

Jeff Reisman

Smartsheet introduced "Record a Date" automation last year, so rather than a formula, an automation rule will probably work best here.

Trigger: When Rows are Changed, when Phone Status changes to Complete, run workflow when triggered

Conditions: Where Citrix Status = Complete AND Network Status = Complete AND PC Status = Complete

Action: Record the date in the Completion Date column.

Rob_PM

Jeff

"Record a Date" automation won't work when the columns are linked to another smartsheet that is updated complete. It will only work if I manually update the columns as I show below. The first row didn't register the "Completion Date" because the "Complete" status comes from a linked smartsheet

I didn't understand your formula:

=IF(Citrix Status = "Complete" AND Network Status = "Complete" AND PC Status = "Complete" AND Phone Status = "Complete", Record the date in the Completion Date column)

Id that the formula?

Rob

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317592#Comment_317592

That wasn't a formula, it was the specifications you would put in the conditions box in the automation rule. I see what you mean about a cell value calculated by formula from another sheet not triggering automation.

Alternative method: Helper column + Automation rule

Create a helper column, call it CompleteCount. Use a formula to count the number of cells equal to "Complete" across those four cells. Then use an Automation trigger to record the date when CompleteCount changes to 4.

In the CompleteCount column:

=COUNTIF([Citrix Status]@row:[Phone Status]@row, "Complete")

Automation Rule:

Rob_PM

Jeff

I did exactly what you told me and the operation is not working. See below:

I even refreshed the page many times. Something is not correct

Please help me

Rob

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317645#Comment_317645

UGH. Still getting nailed by the cell-link restriction, even with the formula on the sheet itself being updated by a change in a different cell-link cell. UGH.

BUT - found a workaround that DOES work!

Change the Automation rule to be one that recurs daily at the end of the day. Set the conditions to be where CompleteCount =4, and CompletedDate is not a date. Then the Action to record the date in the CompletedDate field WILL WORK since it's triggered by time, and not by a cell changed by cell-link.

Rob_PM

Jeff

I noticed the rows without the link, changed but not the one that is linked. I am going to try what you mentioned above

Rob_PM

Jeff

Look what I got when I was setting up the automation. There is no option to setup Daily at the end of the day.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317667#Comment_317667

You have to set the trigger to "When a date is reached", then choose Custom, pick every 1 day, and set for whatever time (choices are on the hour.)

About 50% of my greatest work in Smartsheet involves finding a way to trick the application into doing what I need it to do. Then as Smartsheet's functionality expands, I have to remember to go back and remove the added complex workarounds and simplify by using the new functionality.

Rob_PM

I am going to try and I will let you know.

Thanks a lot!

Rob

Rob_PM

I setup like this

the columns that have links still didn't register the date. I don't get it because the automation is for a column that doesn't have a cell link which is CompleteCount

Jeff Reisman

That's strange. OK I have the exact same rule set up, except that mine is set to run at 11am EST today. I have two rows, one of which contains a formula based on a remote cell link. I updated the remote sheet, which changed the StatusD column to blank. Then I updated the remote sheet again, changing StatusD back to "Complete." The CompleteCount column for both rows has the same COUNTIF formula. Now we wait.

Rob_PM

Let me also setup for 11am EST and I will let you know

Rob

Jeff Reisman

Successful for me:

Rob_PM

I can't believe it, you got it right?

Mine it didn't change. Should I report this to Smartsheet is a bug?

Jeff Reisman

I would definitely contact support, reference this thread. You can share your sheet with them, they can check it out behind the scenes and see what's going on. I've had to do this once before, and there were just things corrupted in the background that they were able to fix.

Our data and automation rule were identical... it makes no sense. (Unless you forgot to refresh your sheet LOL)