Status RGYB balls formula, when due tomorrow and past due
I built out this formula to change the status marker balls colors based on today's date and my "due Date" column:
=IF(Status@row <> "Complete", IF([Completion/ Posting Date]@row > TODAY(10), "Blue", IF([Completion/ Posting Date]@row > TODAY(5), "Green", IF([Completion/ Posting Date]@row > TODAY(2), "Yellow", IF([Completion/ Posting Date]@row >= TODAY(1), "Red")))))
But I can't get it to keep the status as red of anything that is due tomorrow, today, or was due any time in the past if it isn't marked as complete. So if an action item was due 3 days ago and isn't done=red, if it's due tomorrow and isn't done=red, if due today=red. Basically, anything with a date that is due soon or should've been done already should remain red in the marker ball column.
The other colors work as desired.
Best Answers
-
=IF(status@row <> "Complete", IF([Completion/ Posting Date]@row > TODAY(10), "Blue", IF([Completion/ Posting Date]@row > TODAY(5), "Green", IF([Completion/ Posting Date]@row > TODAY(2), "Yellow", IF([Completion/ Posting Date]@row >= TODAY(1), "Red", IF([Completion/ Posting Date]@row <= TODAY(), "RED"))))))
I added the last bit - IF([Completion/ Posting Date]@row <= TODAY(), "RED"
-
I adjusted your formula to address your first question and then added another "IF" statement to address your second question:
=IF([Completion/ Posting Date]@row = "", "", IF(Status@row <> "Complete", IF([Completion/ Posting Date]@row > TODAY(10), "Blue", IF([Completion/ Posting Date]@row > TODAY(5), "Green", IF([Completion/ Posting Date]@row >= TODAY(2), "Yellow", "Red")))))
Basically, the formula will not show a ball if the Completion/Posting Date is blank and will then show a red ball for anything not meeting the criteria for blue, green and yellow.
Hopefully, this will solve your problems.
Good luck!
Answers
-
Try rearranging the order of your IF statements so that the IF outputting "Red" comes before the rest of the other colors.
=IF(Status@row <> "Complete", IF([Completion/ Posting Date]@row >= TODAY(1), "Red", IF([Completion/ Posting Date]@row > TODAY(10), "Blue", IF([Completion/ Posting Date]@row > TODAY(5), "Green", IF([Completion/ Posting Date]@row > TODAY(2), "Yellow")))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
=IF(status@row <> "Complete", IF([Completion/ Posting Date]@row > TODAY(10), "Blue", IF([Completion/ Posting Date]@row > TODAY(5), "Green", IF([Completion/ Posting Date]@row > TODAY(2), "Yellow", IF([Completion/ Posting Date]@row >= TODAY(1), "Red", IF([Completion/ Posting Date]@row <= TODAY(), "RED"))))))
I added the last bit - IF([Completion/ Posting Date]@row <= TODAY(), "RED"
-
Hmm seems to turn everything that had a ball red. anything without a ball even if it had a date didn't change. I did make it a column formula.
-
Thank you @James Keuning that worked!
-
Sorry one more thing, If I wanted to exclude any cells from getting a bubble if the Completion/ Posting Date is blank, I can't seem to get that into the formula. I'm still pretty new to formulas.
-
I adjusted your formula to address your first question and then added another "IF" statement to address your second question:
=IF([Completion/ Posting Date]@row = "", "", IF(Status@row <> "Complete", IF([Completion/ Posting Date]@row > TODAY(10), "Blue", IF([Completion/ Posting Date]@row > TODAY(5), "Green", IF([Completion/ Posting Date]@row >= TODAY(2), "Yellow", "Red")))))
Basically, the formula will not show a ball if the Completion/Posting Date is blank and will then show a red ball for anything not meeting the criteria for blue, green and yellow.
Hopefully, this will solve your problems.
Good luck!
-
@Guy Behanna Thank you that worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!