How do you change cell color automatically based on date

Question

I need every cell ($M$6:$M$84) that is 2 years 6 months from the date in the cell to turn yellow and 2 years 11 months from the date in the cell to turn red. Also any blank cells to be white.

Answer 1

Hi Conditional formatting is what you are looking for.  The first thing to do is determine the number of months between your date.  There are a few ways of doing it like datefiff or the like, but the way I've done it is using the yearfrac formula

=YEARFRAC(NOW(),M6)*12

The formula above uses now() which is a volatile function, that is, it refreshes when the sheet does, so you might want to link this to a specific cell in your worksheet.  This will give you the total months between now and the date in the cell.  You want anything over 2 years 6 months (30 months) to be yellow and anything over 2 years 11  months (35 months) to be red.

Go to Conditional Formatting in Home section on the ribbon and select new rule.  Then use the rule I have in there.  Remember if you are using a cell instead of the now() function you'll need to lock the reference to it eg $a$1.  You should also note that the number part in M6 does not have the $ sign.  This is so that the rule can me applied down the range

You need to add a second rule for the red condition.  You should end up with this

The order is important because if you had the amber one above the red one, because the condition is anything over 30 moths would also be true for dates over 35 moths....so the red rule would never be used.  Changing the order is done with the up and down arrows next to the delete rule.  My rules only work on M6 to M12, obviously you'd have to change that.

Regards,

Tony

Answer 2

What am I doing wrong?

Answer 3

close the bracket after the the $m6

=And(yearfrac(now(), $m6)*12 >30, $m6<>"")