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!