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

Best Answers

  • Michael Mulford
    Answer ✓

    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
    Rob_PM ✭✭
    Answer ✓

    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
    Rob_PM ✭✭
    Answer ✓

    Michael

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

Answers

  • Paul H
    Paul H ✭✭✭✭✭✭

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


  • Rob_PM
    Rob_PM ✭✭
    edited 01/24/22

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

  • Michael Mulford
    Answer ✓

    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
    Rob_PM ✭✭
    Answer ✓

    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
    Rob_PM ✭✭
    Answer ✓

    Michael

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

  • Rob_PM
    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
    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.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!