Excel 2007 : Conditional Formatting, Font Color Change Based on Date

**badaxe** · 03-12-2012, 07:16 PM

I am creating a bank register using Excel but my bank doesn't always release the funds immediately.
Can someone tell me how to change the deposit (J20) font color to red if the available date (G20) is before today?

Thank you.

**Fotis1991** · 03-13-2012, 04:04 AM

Hi

In Conditional formatting of J20, >>Formula is.>>>=G20<TODAY()

Choose red color>>OK.

**badaxe** · 03-13-2012, 03:41 PM

Register Test.xls

Okay, so what am I doing wrong?

**Pepe Le Mokko** · 03-13-2012, 03:53 PM

Everything seems OK. Of course if your reference cells are empty in col G, the CF will fire. An empty cell is 0 which is smaller than today.

**Fotis1991** · 03-13-2012, 03:56 PM

...Okay, so what am I doing wrong?

...G20(in your sample workbook)="".

So, ""(nothing)) is less than Today(). So J20= red color. This is corect.!!!

**Corran** · 03-13-2012, 04:26 PM

Basically, they're saying you need to account for empty cells.

Consider using:

Please Login or Register  to view this content.

as your formula in the conditional format.

If the cell is blank, then see if TODAY() is less than TODAY() (it never will be, so the conditional format won't trigger).
If the cell isn't blank, then see if the date in the G column is less than TODAY(). If it is, trigger the conditional format.

Attached is the updated spreadsheet to help you see the change.