Date Conditional Formatting changing colour based on date below (Excel - MacOS)

Question

I'm trying to make conditional formatting for cells based on how close the deadline is. For example, if the deadline is today, it highlight as red, if it has<=15 days it will show as orange, if it has more than 30 days it will show as green etc.

However, if I update a date in a cell, the colour of the formatting above it changes even though the date remains the same. I'm using these rules in this order:

• Date Occurring - Today
• Cell Value < TODAY()
• Date Occurring - This Week
• Date Occurring - Next Week
• =DATEDIF(TODAY(),D2,"D") <=15
• =DATEDIF(TODAY(),D2,"D") <=30
• =(DAYS(D2,TODAY()) > 30) + 1

I'm very new to using this much conditional formatting and DATEDIF.

Answer 1

Hi there

Regarding, "... Here's what's happening. When I update the cell with '20 February 2022' with '31 March 2022', the cell above it changes color, I'm not sure why. Also, it doesn't happen all the time, only occasionally."

Reasons:

1. The conditional formatting formulas have cell D2 as the starting point, where the applied range is the entire column D (i.e. $D:$D) which obviously includes row 1 (cell D1). This creates an OFFSET of the Conditional formating feature behavior.

Please, refer to the below picture.

Solution: Replace D2 with D1 in all the CF formulas

2. The order of the Conditional Formatting rules should be applied in descending order as mentioned in my previous reply

1st Blank cells

2nd >30

3rd <=30

4th<=15

..... and so on

Solution: Select the rule and use the Up and Down arrows to move/order the rules as needed. Please, refer to the below picture.

3. I choose the =DAYS() formula instead of DATEDIFF

Find in the link below your sample file with the answer

https://we.tl/t-Zi9GC0KRRV

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 2

Hi xDeuiii,

First, my pleasure to assist you.

As per your description, please don’t worry, we can work together to narrow down and resolve the situation.

If you don't mind, could you please upload screenshot of your Excel data view here so we can understand your exact scenario and provide our suggestion in a productive manner, perhaps some related screenshots will be helpful. We may need to check the elements in the formula with the data in your workbook.

I appreciate your understanding and stay safe!!

Best regards

Waqas Muhammad

Answer 3

Hi there

You may try the following formulas and Rules order as indicated in the picture below

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 4

Using this, the cells no longer change colour at all if I update the dates.