Forum Discussion
Conditional Foratting by date
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 ar...
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.
