Dec 29 2022 10:31 AM
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
Dec 29 2022 10:40 AM
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:
=AND(E20<>"",E20<TODAY())
Dec 29 2022 10:57 AM
@OliverScheurichthank you - wow that was quick! Unfortunately it didn't work for me but thank you very much for trying.
Dec 29 2022 12:46 PM
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:
Dec 29 2022 01:32 PM