# I need some help changing my At Risk formula so it doesn't flag when dates are empty

Options
✭✭✭✭
edited 03/25/24

How can I change this formula so that if there are NO start dates and end dates in a row when it is set to Not Started, it does NOT show as flagged. Here is what it is currently doing:

Here are the formulas I currently have set up for Status: =IF(ISBLANK(Complete@row), "", IF(Complete@row = "Not Started", "Gray", IF(Complete@row = "Complete", "Green", IF(AND([End Date]@row < TODAY(0), ISDATE([End Date]@row)), "Red", IF(AND([End Date]@row = TODAY(0), ISDATE([End Date]@row)), "Yellow", IF(OR(ISBLANK([End Date]@row), ISBLANK([Start Date]@row)), "Green", IF([End Date]@row <= TODAY(7), "Yellow", "Green")))))))

And the formula for At Risk: =IF([Helper at Risk]@row <> 1, IF(AND([End Date]@row <= TODAY(0), Complete@row <> "Complete", Status@row <> ""), 1, 0), IF(COUNTIFS(CHILDREN(), 1) > 0, 1, 0))

Tags:

• ✭✭✭✭✭
Options

Hi @Alicia D, I would modify your At Risk formula... Here it is written out:

=IF(Complete@row = "Not Started", 0,IF([Helper at Risk]@row <> 1, IF(AND([End Date]@row <= TODAY(0), Complete@row <> "Complete", Status@row <> ""), 1, 0), IF(COUNTIFS(CHILDREN(), 1) > 0, 1, 0)))

In other words, set the flag to 0 if the project is Not Started. Otherwise use the same logic as before. I'm assuming the rest of the formula works as you would like it to.

Be well

• ✭✭✭✭✭
edited 03/25/24
Options

Hi @Alicia D , Could you just add an IF() to the beginning of your At Risk formula like the following?

=IF(Complete@row = "Not Started", 0,....

or

=IF([Start Date]@row = "", 0,....

Hope this helps. Be well.

• ✭✭✭✭
Options

@Scott Orsey would that be before my at risk formula or before the completion formula?

• ✭✭✭✭✭
Options

Hi @Alicia D, I would modify your At Risk formula... Here it is written out:

=IF(Complete@row = "Not Started", 0,IF([Helper at Risk]@row <> 1, IF(AND([End Date]@row <= TODAY(0), Complete@row <> "Complete", Status@row <> ""), 1, 0), IF(COUNTIFS(CHILDREN(), 1) > 0, 1, 0)))

In other words, set the flag to 0 if the project is Not Started. Otherwise use the same logic as before. I'm assuming the rest of the formula works as you would like it to.

Be well