It works fine in the US using excel 2003. Which versoin of excel are you
using and which country are you in. I copied the formulas from you posting.
I also copied the 28th feb 07 and didn't get errors. check the cell where
you have the conditional formating and make sure it is formated as a DATE.

"singingsister" wrote:

thanks for that Joel. I've amended it but it's still not working properly.
It's putting dates like 28th feb 07 in red and dates in april in amber?!!!

should just be 1 week away from todays date in red and 14 days in amber.

"Joel" wrote:

The two place where you have $O223 there is the digit zero instead of the
letter O (J,K,L,M,"O").

"singingsister" wrote:

I posted a question a few weeks ago about a spreadsheet of dates I have. I
have tried to do the formula on the actual spreadsheet (did "testing" on
dummy sheet before I was certain it worked and got approval from boss to
use), but, now I've tried to do the formula again and it doesn't work!

To cut a long story short, my spreadsheet has 3 columns I would like to
track.

Column K is the requested delivery date from the customer, column L is the
quoted delivery date and column M is the factory acknowledged delivery date.
What I would like to happen, is that if the date in either of the columns
reaches between 14 days and 7 days away from todays date it flags yellow (as
a warning) and when the date reaches 7 days or less away from todays date the
colour changes to red (as a visual warning to chase delivery).

What I also would like to do is that when I put a date in column O (which is
the actual despatch date) the colours in that row for the 3 columns above
disappear.

The spreadsheet is a working document so I will need to apply the formula to
all rows as it gets added to on a daily basis.

The formula I was using was to flag red was
=and(k2230,k223=today(),k223-today()<=5,$0223="") and to flag amber i used
=and(k2230,k223=today(),k223-today()<=14,$0223="")

PLEASE can someone help me out of my pickle. The formula did work?! Promise!