# Symbols Color Change based on Dates

✭✭✭✭

Hello,

I'm looking for a formula that will automatically change based on close we are to the due date.

So let's say the following:

30 days till the live date, Green

15 days till the live date, Blue

5 days till the live date, Yellow

under 5 days, red

Tags:

• ✭✭✭✭✭

Hi Meny,

Nested IF( ) statements utilizing the TODAY( ) function will be your best bet here. I had to make some assumptions based on the conditions you defined above, but this should work for you:

`=IF([Due Date]@row < TODAY(5), "Red", IF([Due Date]@row = TODAY(5), "Yellow", IF(AND([Due Date]@row > TODAY(5), [Due Date]@row < TODAY(30)), "Blue", IF([Due Date]@row >= TODAY(30), "Green"))))`

This formula assumes the following:

• Green for any due date that is 30 or more days from today
• Blue for any due date that is less than 30 but more than 5 days from today
• Yellow for any due date that is exactly 5 days from today
• Red for any due date that is less than 5 days from today

Feel free to modify as needed for your use case.

-MS

• ✭✭✭✭

Thank you!

• ✭✭✭✭

Thank you!

• ✭✭✭✭✭

Hi Meny,

Nested IF( ) statements utilizing the TODAY( ) function will be your best bet here. I had to make some assumptions based on the conditions you defined above, but this should work for you:

`=IF([Due Date]@row < TODAY(5), "Red", IF([Due Date]@row = TODAY(5), "Yellow", IF(AND([Due Date]@row > TODAY(5), [Due Date]@row < TODAY(30)), "Blue", IF([Due Date]@row >= TODAY(30), "Green"))))`

This formula assumes the following:

• Green for any due date that is 30 or more days from today
• Blue for any due date that is less than 30 but more than 5 days from today
• Yellow for any due date that is exactly 5 days from today
• Red for any due date that is less than 5 days from today

Feel free to modify as needed for your use case.

-MS

• ✭✭✭✭

Thank you!

• ✭✭✭✭

If the task has been check as done, they turn green regardless of the due date?

• ✭✭✭✭✭✭

I've added that if the Due Date is blank, it won't show as red, and if Done is checked, it will be green.

=IF(Done@row = 1, "Green", IF([Due Date]@row = "", "", IF([Due Date]@row < TODAY(5), "Red", IF([Due Date]@row = TODAY(5), "Yellow", IF(AND([Due Date]@row > TODAY(5), [Due Date]@row < TODAY(30)), "Blue", IF([Due Date]@row >= TODAY(30), "Green"))))))

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭

Thank you!

• ✭✭✭✭✭✭

Happy to help!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!