Symbols Color Change based on Dates

Meny Hoffman

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

Thanks in advance.

Mark Safran

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

Meny Hoffman

Thank you!

Meny Hoffman

Thank you!

Mark Safran

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

Meny Hoffman

Thank you!

Meny Hoffman

Can I add something more?

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

Andrée Starå

Hi @Meny Hoffman

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

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Meny Hoffman

Thank you!

Andrée Starå

@Meny Hoffman

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!