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

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

Answer 3

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

In order to help you

Kindly suggest preparing and uploading a sample file to Onedrive, Dropbox, etc ... 

and share the link here.

It will help us to give you a prompt and right solution.

If you need help with how to upload the file please, click the link below

https://support.office.com/en-us/article/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

You may also follow the instructions in this video

https://www.youtube.com/watch?v=NnXsE0SNuCc&t=14s

Regards

Jeovany

Answer 4

Here's the test file I'm using. Column D is the one I made, column G has the suggestion from Jeovany CV https://1drv.ms/x/s!AnJ8-P2HJUB-iEZNf0JHKK18Ti-Y?e=mN1jsh