Conditional Formatting comparing two dates

Ksephora

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.

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

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.