Determine follow-up date that will trigger Workflow

SherryFox

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

Hunter Taylor

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!

Hunter Taylor

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!

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!!!

Hunter Taylor

Of course! I'm glad I could help.

SherryFox

@Hunter Taylor ,

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