How do I utilize conditional formatting to highlight cells that are past due at different levels?

I want to highlight cells in my due dates column that are past due, current and upcoming. For example if the due date is past due by 21 days, I want that cell to be highlighted red, past due 14 days, highlight orange, past due only 7 days, highlight yellow. If the due date is in the future, I want it highlighted blue. I know I can use conditional formatting to highlight cells based on past and future, but is there a formula I can use to determine different color levels based on the number of days the due date is past due?

Thank You!

Find more posts tagged with

Accepted answers

All comments

Andrée Starå

Hi @Allison Bradshaw

I hope you're well and safe!

You'd need to add a so-called helper column with a formula to calculate the number of days or similar and then reference it in the Conditional Formatting rule.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

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

Allison Bradshaw

Thank you Andre. Can you please advise on the best formula for my helper column? Would it be something like the below? How do I incorporate a additional if/then statements into the same formula?

=IF([Due Dates]@row=TODAY()-7, Past Due by 7, 0)

Andrée Starå

@Allison Bradshaw

Happy to help!

Try something like this, and then you can use the conditions in the Conditional Formatting for multiple options. (21, 14, 7 days)

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. 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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.