Can anyone tell me what's wrong with my formula?

Camille Armitstead

I'm trying to set up a project plan that has color indicators for the timeline: RED=past due, Yellow=due in the next 5 days, Green=due in the next 14 days, Blue=due later.

=IF([Due Date]2 < TODAY(), "Red", IF(AND([Due Date]2 >= TODAY(), [Due Date]2 < TODAY(+5))), "Yellow", IF(AND([Due Date]2 >= TODAY(), [Due Date]2 < TODAY(+14))), "Green", "Blue")

Thank you so much in advance!

Matt Johnson

Hi @Camille Armitstead

I think the formula you are looking for is this one. I used the Smartsheet AI Formula generator for this. I copied and pasted your words "RED=past due, Yellow=due in the next 5 days, Green=due in the next 14 days, Blue=due later." Then changed RED to Red and the AI did the rest. Maybe I deserve partial credit for this one.

=IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY() + 5, "Yellow", IF([Due Date]@row <= TODAY() + 14, "Green", "Blue")))

I hope that helps.

Matt

Matt Johnson

Hi @Camille Armitstead

I think the formula you are looking for is this one. I used the Smartsheet AI Formula generator for this. I copied and pasted your words "RED=past due, Yellow=due in the next 5 days, Green=due in the next 14 days, Blue=due later." Then changed RED to Red and the AI did the rest. Maybe I deserve partial credit for this one.

=IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY() + 5, "Yellow", IF([Due Date]@row <= TODAY() + 14, "Green", "Blue")))

I hope that helps.

Matt

Camille Armitstead

THANK YOU!!! You just made my week!

Paul Newcome

The only thing technically wrong with your original formula is that you have two misplaced closing parenthesis. After TODAY(+5) and TODAY(+14). One should be taken from each and put on the end.

=IF([Due Date]2 < TODAY(), "Red", IF(AND([Due Date]2 >= TODAY(), [Due Date]2 < TODAY(+5)), "Yellow", IF(AND([Due Date]2 >= TODAY(), [Due Date]2 < TODAY(+14)), "Green", "Blue")))

While you don't NEED to use the AND functions if you write it in a specific order, it doesn't hurt to have them as long as the syntax is correct.

Camille Armitstead

Excellent advice, thank you!!