Conditional Formatting comparing two dates

I'd like to create a conditioning formatting to highlight a cell based on the difference between two dates. Column A is the expected date of delivery. Let's say 1/1/23. Column B is the actual date I received it, say 1/5/23. I'd like it to follow this rule:

Highlight Column A cell when:

Received date is more than 5 days before expected date = green highlight

Received date between 5 days before and 5 days after expected date = yellow highlight

Received date is more than 5 days after expected date = red highlight

This was I can track if materials are being delivered in a timely manner.

Find more posts tagged with

Conditional Formatting

Accepted answers

JamesB

@Ksephora You would need to create a helper column to first calculate the days before/after received date. You can then use this column as the condition for the formatting.

DKazatsky2

Hi @Ksephora,

First, create a helper column to get the amount of days in-between the dates. Use this formula (make it a column formula).

=[Received Date]@row - [Expected Date]@row

Next, setup 3 conditional formats.

Here is what it looks like - make sure to set the formatting on the correct column for your needs.

Hope this helps,

Dave

All comments

JamesB

@Ksephora You would need to create a helper column to first calculate the days before/after received date. You can then use this column as the condition for the formatting.

DKazatsky2

Hi @Ksephora,

First, create a helper column to get the amount of days in-between the dates. Use this formula (make it a column formula).

=[Received Date]@row - [Expected Date]@row

Next, setup 3 conditional formats.

Here is what it looks like - make sure to set the formatting on the correct column for your needs.

Hope this helps,

Dave

Ksephora

DKazatsky2 and JamesB thanks so much! That worked perfect.

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.