Conditional Formatting a Row Based on a Date Range

[Chris Waller]

I am trying to apply conditional formatting that will highlight a row from column A to O if the following conditions are met.

=$K3<= today () this should highlight the row with grey shading
This is to highlight any row where the date has passed

=And($K3-today()+20,$K3-today()<=26) which should highlight the row in orange
This is to highlight any date between 20 days and 26 days in the future

=And($K3-today()+27,$K3-today()<=33) which should highlight the row in yellow
This is to highlight any date between 27 days and 33 days in the future

Column K contains the date.

I have tried altering the order of the three conditions and no matter what I do there are rows that are highlighted that shouldn’t be. The grey shading appears to work and I think the yellow shading appears to work, but the orange shading doesn't appear to work as there are rows highlighted that shouldn't be.

Thanks in advance

 

[arthurbr]

Perhaps
=And($K3>=today()+20,$K3-today()<=26)
=And($K3>=today()+27,$K3-today()<=33)

 

[Chris Waller]

Thanks for the formula it really helped me sort out the problem, however, I did have to tweak the second part of each formula i.e.

$K3-today()<=26) to $K3<=today()+26
$K3-today()<=33) to $K3<=today()+33

I was only able to do this because you supplied the information for the start of the formula.

Thanks once again. I did manage to get it to work. Brilliant.