=and(N2230,N223=today(),N223-today()<=5,$[new column]223="")
change the N to O and P for the other two columns
and replace the [new column] with whatever column the dispatch date is in (R?)
"singingsister" wrote:

OK - the spreadsheet has now been changed majorly by a manager so the columns
are not the same anymore.

Please can someone tell me what the formula is from scratch please.

The three date columns are now N,O and P rather than K,L and M.

"bj" wrote:

The equation looks OK

in a cell try
=k223-today()
Are you getting what you expect?
try changing the date in k223
are you sure you are in row 223 for this conditional equation?
(I lost almost a full day one time because I was one row off in my
conditional references, and I did not check the obvious, since I "wouldn't"
make that kind of mistake))
If the conditional formating does not have unwanted absolute or relative
references, incorrect references or unwanted Quote marks, it has to be either
a date formatting problem or an error in the way your Excel file is handling
dates.

If everything looks OK and the equation works, close out of excel reopen
and try again on a clean excel sheet and then the one with which you are
having problemns. If it does not work on the new sheet, borrow someone's
computer and enter the format equation in an new excel file and see what
happens.

"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!