Sign in to join the conversation:
Hello,
Based on example from sheet provided, I'm looking for formula to populate in the At Risk column to flag as At Risk if Final Due Date is seven days out and the Status does not equal "Yes" and % Complete is not 100%
Any support is appreciated!
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
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:
I hate to ask, work?
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,
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,
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 Cronk
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?
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?
Date Report Partner CBO Name 01/12/25 Children's org 05/15/25 Foodbank 08/30/25 Children's org 09/11/25 Chamber 11/25/25 Digital Bus 08/21/24 Digital Bus South 01/15/26 Children's org 01/12/24 Foodbank Sister 02/15/24 For the children org 07/01/26 Digital Bus #2 08/05/26 Family Center 12/01/26 Family Center I am trying to…
I am trying to get the passenger count per month per year, and I can't seem to get the formula correct. I need to add the number of passengers for each month per year. If anyone could assist me with this, I would greatly appreciate it. Thanks!
I need to edit a large sheet that has a lot of predecessors. I need to delete one row and move another. when I make the changes the predecessors for proceeding lines are not correctly displaying the dates correctly. Need help.
