Morning all.

Are there any excel conditional formatting gurus in the house today?

I’m trying to turn a cell green or red depending on the date in that cell.

If the date has passed then it needs to be red, and if the date is in the future then it needs to be green. The cell would also need to change from green to red as the calendar date changes.

Is such a piece of excel sorcery possible? I’m using Excel 2010.

Ta in advance

If you add 2 rules to the Conditional Formatting based on the cell value, one being Cell Value < =now() and the second being Cell Value > =now() – should give you what you need?
RM.

I’d use TODAY() to get a clearer cutoff, because a hard coded 31/10/2014 is less than NOW() so you’d shade today as being red which I don’t think is what you want ?

NOW() returns date & time, TODAY() just returns the date. Or more specifically, 00:00 on the morning of that day.

Ta RM

Managed to work it out:

=J59>TODAY()-365 (for the green colouring)
=J59<TODAY()+365 (for the red colouring)

That’s not going to do what you asked for ! Ditch the ±365 bits.

What @Sunday says

Cheers Sunday. That’s it sorted.