Status
Hello!
I'm trying to create a formula based on status color for my office Smartsheet for 2022. It's blank, but I've been putting in test data to try it out. I haven't done this in so long so I'm out of practice, and used the old formula as reference from my past job. It's all showing up as Blue, no matter what date I put in.
Here's what I'm trying to do: ("Due Date" is a Date format)
Blue = Done (from the "Done" column that's a checkbox)
Red = 3 days prior to due date
Yellow = 5 days prior to due date
Green = 7 days prior to due date
Here's the formula I used:
=IF(ISBLANK([Due Date]@row), "", IF(ISBOOLEAN(Done@row), "Blue", IF(TODAY() >= [Due Date]@row + 3, "Red", IF(TODAY() >= [Due Date]@row + 5, "Yellow", IF(TODAY() >= [Due Date]@row, "Green")))))
Any suggestions?
I appreciate your help in advance! Thanks so much!
Best Answer
-
@fennerb looks like you've got an extra parentheses at the end of your formula! Easy fix on that error.
Also- I was toying around with this formula and figured I could help save you a headache after the #UNPARSEABLE. You'll want to wrap an AND function inside of your IF statements to encompass a specified date range. Additionally, you would want to subtract 3 or 5 from the due date instead of add. If you add the range you're looking to update your status in, then your colors won't change until after the due date has passed. Here's the formula I've got...
=IF(ISBLANK([Due Date]@row), "", IF(Done@row = 1, "Blue", IF(AND(TODAY() >= [Due Date]@row - 3, TODAY() <= [Due Date]@row), "Red", IF(AND(TODAY() >= [Due Date]@row - 5, TODAY() < [Due Date]@row - 3), "Yellow", IF(TODAY() < [Due Date]@row - 5, "Green")))))
Let me know how this works :)
Answers
-
Hi there!
Is your Done column a checkbox? The ISBOOLEAN formula would just check to see whether or not the cell contains a checkbox, not whether or not the box is checked. If you are trying to return blue when the Done box is checked, try this...
=IF(ISBLANK([Due Date]@row), "", IF([Done]@row = 1, "Blue", .......
Additionally, you might want to take another peek at your date criteria. Whenever I have done status updates based on rate ranges in the past, I usually use an AND within the IF statement to encompass both the start and end of the date range used.
Hope this helps!!
Cheers,
Brandon
-
@Brandon Vanlandingham My Done column is most definitely a checkbox. :)
Working on the AND with the IFs right now. I tried the change regarding the checkbox info suggested, and it came back as #UNPARSEABLE. :(
=IF(ISBLANK([Due Date]@row), "", IF([Done]@row = 1, "Blue", IF(TODAY() >= [Due Date]@row + 3, "Red", IF(TODAY() >= [Due Date]@row + 5, "Yellow", IF(TODAY() >= [Due Date]@row, "Green"))))))
-
@fennerb looks like you've got an extra parentheses at the end of your formula! Easy fix on that error.
Also- I was toying around with this formula and figured I could help save you a headache after the #UNPARSEABLE. You'll want to wrap an AND function inside of your IF statements to encompass a specified date range. Additionally, you would want to subtract 3 or 5 from the due date instead of add. If you add the range you're looking to update your status in, then your colors won't change until after the due date has passed. Here's the formula I've got...
=IF(ISBLANK([Due Date]@row), "", IF(Done@row = 1, "Blue", IF(AND(TODAY() >= [Due Date]@row - 3, TODAY() <= [Due Date]@row), "Red", IF(AND(TODAY() >= [Due Date]@row - 5, TODAY() < [Due Date]@row - 3), "Yellow", IF(TODAY() < [Due Date]@row - 5, "Green")))))
Let me know how this works :)
-
@Brandon Vanlandingham IT WORKS! Thank you so much!
You've most definitely saved me a headache, for sure.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!