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:
- If the certification Complete is GREEN, this date can remain blank, as no follow-up will be sent.
- If the certification Complete is YELLOW or RED, the Manager has either partially completed (yellow) or not completed at all (red).
- The workflow initiated will be manually entered, when we add names
- 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
-
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:
- Condition for non-green certification:
[Certification Complete]@row <> "Green"
ensures the certification is not marked as complete (Green).
- Condition for Workflow Initiated:
NOT(ISBLANK([Workflow Initiated]@row))
ensures there is a date in the "Workflow Initiated" field.
- Condition for 14 days elapsed:
[Workflow Initiated]@row <= TODAY(-14)
checks if the "Workflow Initiated" date is more than 14 days old.
- Populate "Workflow Follow-up":
- If all the above conditions are met, it populates with
TODAY()
(current date). Otherwise, it remains blank (""
).
- If all the above conditions are met, it populates with
Implementation Steps:
- Copy the formula above.
- Add it to the "Workflow Follow-up" column as a column formula or in the specific rows where you need it.
- 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
- Condition for non-green certification:
Answers
-
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:
- Condition for non-green certification:
[Certification Complete]@row <> "Green"
ensures the certification is not marked as complete (Green).
- Condition for Workflow Initiated:
NOT(ISBLANK([Workflow Initiated]@row))
ensures there is a date in the "Workflow Initiated" field.
- Condition for 14 days elapsed:
[Workflow Initiated]@row <= TODAY(-14)
checks if the "Workflow Initiated" date is more than 14 days old.
- Populate "Workflow Follow-up":
- If all the above conditions are met, it populates with
TODAY()
(current date). Otherwise, it remains blank (""
).
- If all the above conditions are met, it populates with
Implementation Steps:
- Copy the formula above.
- Add it to the "Workflow Follow-up" column as a column formula or in the specific rows where you need it.
- 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
- Condition for non-green certification:
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!