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
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
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.
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!

@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
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!

@Scott Orsey this worked, thank you!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 321 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!