I need some help changing my At Risk formula so it doesn't flag when dates are empty
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))
Best Answer

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
Answers

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.

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

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

@Scott Orsey this worked, thank you!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!