Conditional Formatting: Changing Cell Color Based on 3 Separate Date Ranges

JustinCase

Hello All,

I'm trying to set up conditional formatting where colors change based on how many days they are away from an automatically generated reference date (Helper Column).

Currently I have it set up so that in when the reference date is in 15 days, the "date certified" column will turn yellow, in 5 days it will turn red, and if it is past due it will turn black. I would like it so that if the date certified is Blank then the formatting for 15 days and 5 days will trigger, but if anything ends up in the black it needs to stay black no matter what. From what I can tell, I did everything correctly but it seems to be hit or miss when I input test dates to see the formatting. Any guidance would be greatly appreciated!

JustinCase

Some additional Info

Cory Page

@JustinCase I set something up real quick and I think it might be better to use a new column that tracks the days rather than working only in the conditional formatting.

The below setup is based on if "Date Certified " Is blank then use Today's date..

"I would like it so that if the date certified is Blank then the formatting for 15 days and 5 days will trigger"

First I calculated the days to make it easier to set up conditional formatting.

=IF([QSE 1 Reference Date]@row - [QSE 1 Date Certified]@row = "", [QSE 1 Reference Date]@row - TODAY(), SUM([QSE 1 Reference Date]@row - [QSE 1 Date Certified]@row))

Then I used between criteria to help ensure that 5 didn't interact with 15

Is this on the right track? I am sure we can do something without the helper but it makes things a tad easier for sure.