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!