At Risk Flag
Comments
-
Ooops. Let me go back to the logic you want:
Blue = Complete, so %Complete =1
Yellow=Not Started, so %Complete =0
Red = Delayed - Finish@row<[Due Date]@row=TODAY(7)? I took this to mean that if the finish date is after the due date or if we are within 7 days of the due date you want it Red.
Green = In Progress with is Not any of the above
Here's the formula I get:
=IF([%Complete]@row = 1, "Blue", IF([%Complete]@row = 0, "Yellow", IF(Finish@row > [Due Date]@row, "Red", IF(TODAY(7)>[Due Date]@row , "Red", "Green")))))
Are we closer? If you get an error, what error do you get?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Let's take a different approach with your At Risk column. Try this formula:
=IF(AND([due date]@row <= TODAY(7), [%Complete]@row < 1), 1, IF(AND([due date]@row >= TODAY(), [%Complete]@row < 1), 1, IF(Status@row = "Red", 1, 0)))
It Flags at At Risk if:
- The due date is 7 days or less away and it's not complete
- The due date is today or in the past and it's not complete
- the project is delayed as determined by Red in the Status
I hate to ask, work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Mark- I greatly appreciate your patience with my inexperience with Smartsheet formulas especially this one. Oh my, can't believe how long it took to crack the code but here it is and it works. Finally! I refused to give up. I definitely need to take a break.
The formula for RGYB Status column based on Finish, Due Date and %Complete columns is :
=IF([%Complete]@row = 1, "Blue", IF([%Complete]@row = 0, "Yellow", IF(Finish@row > [Due Date]@row, "Red", IF(TODAY(7) > [Due Date]@row, "Red", "Green"))))
I removed a parenthesis off the end and it works.
See highlighted area in screenshot.
-
Well done. Happy to help along the way. Thanks for using the Community.
Until next time,
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Not sure about At Risk formula yet. The row is flagged when the Finish column date is blank. See highlighted screenshot.
-
Found the mistake. The second AND statement was greater than today. As I look at it, we didnt need that statement. Try:
=IF(AND([due date]@row <= TODAY(7), [%Complete]@row < 1), 1, IF(Status@row = "Red", 1, 0))
Thanks for making me think and allowing me to help.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Mark,
This has been a journey and w/out you, idk but greatly appreciate you hanging in here with me. IT WORKS! See screenshot. Can I reach out to you in the future for this item if I need additional help? Thank you for all of your time and help on these 2 formulas. Also, be safe, warm and enjoy the holidays!
-
Hi @Cee Johnson ,
Look beautiful. Well done. I'm happy to help anytime. Just tag me in your post so I see it.
Be Well,
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi Mark,
Hope all is well. I created tasks with 2021 dates on a project plan but I don't see the dates on the Gantt chart. Please confirm if this is correct or there's something I need to do for it to display on the Gantt.
-
Good afternoon @Cee Johnson ,
A Gantt chart requires 2 date columns. Congirm you column are formatted as dates. Then, click the Project Settings icon in the Gantt chart header to ensure the Gantt chart is using the correct columns for start and end date.
More on Gantt charts here:
FInd your problem?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi Mark,
I often don't see your comments right away and sometimes not until hours later. Guess the page has to be refreshed. The columns setting is for dates but the Gannt chart still doesn't show 2021 dates. Only 2020.
-
I am using this formula to set the at Risk Flag
=IF(AND([Due Date]@row < TODAY(), NOT(Status@row = "Complete")), 1, 0)
However, Rows with no date (like a summary row) will set to 1 because the date is blank. How do I tell the formula to ignore a blank cell?
-
You can look for a date with ISDATE or for a blank with ISBLANK
Try:
=IF(AND(ISDATE([due date]@row), [Due Date]@row < TODAY(), NOT(Status@row = "Complete")), 1, 0)
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!