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")))))
-
=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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!