Status

fennerb

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!

Brandon Vanlandingham

@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

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

fennerb

@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"))))))

Brandon Vanlandingham

@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 :)

fennerb

@Brandon Vanlandingham IT WORKS! Thank you so much!

You've most definitely saved me a headache, for sure.