Conditional Foratting by date

New Contributor

I would like every cell that contains a date before today to change colour.  I've used:


Conditional Formatting / Highlight Cells Rules / Less Than / =TODAY() / specify colour


but all cells are the same colour irrespective of the date in the cell.


I've also tried:


Conditional Formatting / New Rule / Format only cells that contain / Less Than / =TODAY()


Again every cell is the same colour irrespective of the date in the cell.


Am I doing something wrong here?


Thank you

4 Replies


You can try this if the "applies to" range is for example A1:F10:


Conditional Formatting / New Rule / Use a formula to determine which cells to format / Format values where this formula is true /  =AND(A1<>"",A1<TODAY())


If the "applies to" range is E20:R40 then you can try this formula for conditional formatting:





@Quadruple_Pawnthank you - wow that was quick! Unfortunately it didn't work for me but thank you very much for trying.


If I understand correctly what you want to do, it would be complicated/a lot of work, since you'd have to create a separate rule for each color that you want to use.

An alternative could be to color the cells next to the dates according to how many days before the current date they are.

In the screenshot below, the dates are in D2:D31.


E2 contains the formula =IF(D2<TODAY(),TODAY()-D2,"") and this has been filled down to E31.

I applied a 3-color scale to E2:E31:


Hans, thank you for your reply. I made a silly mistake by entering the date format as 29.12.22. If I type 29/12/22 instead, it all works ok. Even with the cell date format set to 29.12.22 it still only works if I use 29/12/22. So (/) works and (.) doesn't but at least I know now.