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.

Find more posts tagged with

Formulas

Accepted answers

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!

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.