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?
Hello I could use some help with this one. I got the formula to work in the "number column"…. but I cant currently get it to work as a column formula. Basically what I am trying to achieve is have it add a number in order if the "footer comp" column is unchecked. and miss is unchecked Right now it is counting but in the…
I'm need to apply a function to average a column (answer is correct (8)) if the other column (The nurse is caring for a a patient who dove into a) is NOT blank. I copied this over from an excel spreadsheet. I would like to keep the average in the top row. Right now it's saying "#unparseable" as the formula is not the same…
This discussion was created from comments split from: How do I combine multiple INDEX Collect formulas in one Formular?.
