Determine follow-up date that will trigger Workflow

I want a date to populate a field that will trigger a follow-up workflow. My criteria is:

  1. If the certification Complete is GREEN, this date can remain blank, as no follow-up will be sent.
  2. If the certification Complete is YELLOW or RED, the Manager has either partially completed (yellow) or not completed at all (red).
  3. The workflow initiated will be manually entered, when we add names
  4. If we have Yellow or Red status AND the initiated date is over 14 days old the Follow-up date should populate with a date, and that will trigger the workflow.

=IF([Certification Complete]@row <> "Green", IF([Date Certified]@row = "", [Workflow Initiated]@row <= TODAY(-14), 1))

Sherry Fox

Business Process Analyst 3 | C5ISR Group

HII | Mission Technologies

EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024

NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox

Connect with me on LinkedIn

Best Answer

  • Hunter Taylor
    Hunter Taylor ✭✭✭
    Answer ✓

    Hi SherryFox,

    I believe the formula you're attempting has some issues, and I have a revision that should fit your criteria.

    Here’s an updated version of the formula to calculate the "Workflow Follow-up" date:

    =IF(AND([Certification Complete]@row <> "Green", NOT(ISBLANK([Workflow Initiated]@row)), [Workflow Initiated]@row <= TODAY(-14)), TODAY(), "")
    

    Explanation:

    1. Condition for non-green certification:
      • [Certification Complete]@row <> "Green" ensures the certification is not marked as complete (Green).
    2. Condition for Workflow Initiated:
      • NOT(ISBLANK([Workflow Initiated]@row)) ensures there is a date in the "Workflow Initiated" field.
    3. Condition for 14 days elapsed:
      • [Workflow Initiated]@row <= TODAY(-14) checks if the "Workflow Initiated" date is more than 14 days old.
    4. Populate "Workflow Follow-up":
      • If all the above conditions are met, it populates with TODAY() (current date). Otherwise, it remains blank ("").

    Implementation Steps:

    1. Copy the formula above.
    2. Add it to the "Workflow Follow-up" column as a column formula or in the specific rows where you need it.
    3. Ensure that the "Certification Complete" column uses the exact status names (e.g., "Green", "Yellow", "Red").

    If this doesn't work as intended, let me know, and we can adjust further!

    Ask Me About Smartsheet Maps?!?!

    Solving Automation, Integration, & Adoption Problems For Smartsheet Customers

    Account Executive | Skyway Consulting Co

    Explore The Possibilities of Smartsheet & ArcGIS

Answers

  • Hunter Taylor
    Hunter Taylor ✭✭✭
    Answer ✓

    Hi SherryFox,

    I believe the formula you're attempting has some issues, and I have a revision that should fit your criteria.

    Here’s an updated version of the formula to calculate the "Workflow Follow-up" date:

    =IF(AND([Certification Complete]@row <> "Green", NOT(ISBLANK([Workflow Initiated]@row)), [Workflow Initiated]@row <= TODAY(-14)), TODAY(), "")
    

    Explanation:

    1. Condition for non-green certification:
      • [Certification Complete]@row <> "Green" ensures the certification is not marked as complete (Green).
    2. Condition for Workflow Initiated:
      • NOT(ISBLANK([Workflow Initiated]@row)) ensures there is a date in the "Workflow Initiated" field.
    3. Condition for 14 days elapsed:
      • [Workflow Initiated]@row <= TODAY(-14) checks if the "Workflow Initiated" date is more than 14 days old.
    4. Populate "Workflow Follow-up":
      • If all the above conditions are met, it populates with TODAY() (current date). Otherwise, it remains blank ("").

    Implementation Steps:

    1. Copy the formula above.
    2. Add it to the "Workflow Follow-up" column as a column formula or in the specific rows where you need it.
    3. Ensure that the "Certification Complete" column uses the exact status names (e.g., "Green", "Yellow", "Red").

    If this doesn't work as intended, let me know, and we can adjust further!

    Ask Me About Smartsheet Maps?!?!

    Solving Automation, Integration, & Adoption Problems For Smartsheet Customers

    Account Executive | Skyway Consulting Co

    Explore The Possibilities of Smartsheet & ArcGIS

  • SherryFox
    SherryFox ✭✭✭

    @Hunter Taylor ,

    That is exactly what I needed. I just could not map it out in my head like in your explanation, however that was exactly my goal. I created the workflows and set the Certified by as me so I can test the workflows. Thanks for your help. If I have any issues with the workflows, then I will reach out. I have my workflows reset for the time so they will get sent at 11am EST. I will leave this open until I get the workflow, so everything can be on one thread. Thanks again so very much!!!

    Sherry Fox

    Business Process Analyst 3 | C5ISR Group

    HII | Mission Technologies

    EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024

    NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox

    Connect with me on LinkedIn

  • Of course! I'm glad I could help.

    Ask Me About Smartsheet Maps?!?!

    Solving Automation, Integration, & Adoption Problems For Smartsheet Customers

    Account Executive | Skyway Consulting Co

    Explore The Possibilities of Smartsheet & ArcGIS

  • SherryFox
    SherryFox ✭✭✭

    @Hunter Taylor ,

    Test workflow worked perfectly. I could not have done this without you Thanks!!!!

    Sherry Fox

    Business Process Analyst 3 | C5ISR Group

    HII | Mission Technologies

    EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024

    NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox

    Connect with me on LinkedIn

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!