David,

I can't go and change K223 as it's a working document and would change the
date . I was originally told what formula to use in the conditional
formatting from another website (can't remember which one) but cell K223
bears no relevance to the overall spreadsheet...it's just a date with regards
to one order, each order is separate to each other.

All I want to do is if the date in the cell is 14 days or less away to be
amber, but when the date in the cell becomes 7 days or less it goes red.

If you need to amend the formula please do!



"David Biddulph" wrote:

Right!

You were previously telling us that you had 28 Feb 2007 in K223 when it was
shown as red. If you've got today's date in K223 then it should indeed be
showing as red.

Now go back and change K223 to 28 Feb 07 and see what happens to the cell
format and to the intermediate parameters which we've been looking at.

As almost always, if you are getting the wrong answer, it is usually because
the wrong question has been asked.
[Note that where you are looking at =K223-TODAY(), it is better to format as
General or Number, as you are looking at a number of days (zero in this
case), rather than at a date as such.]

One further thing to check if you are getting unexpected answers is to make
sure that under Tools/ Options/ Calculation you have the mode set to
Automatic.
--
David Biddulph

"singingsister" wrote in message
...
David,
=K223 gives me today's date
=today gives me today's date
and yes
=(K223=TODAY()) gives me true

=K223-TODAY()) gives me 00 January 1900

"David Biddulph" wrote:

So you are telling us that that
=K223 returns 39141
=TODAY() returns 39232
and that
=(K223=TODAY()) returns TRUE ?

O.K., now humour us one stage further, tell us what =K223-TODAY()
returns?
--
David Biddulph

"singingsister" wrote in
message
...
David,

I broke it down as you suggested and the only one where it differs is
=(K223=TODAY()) as I get TRUE as the answer.

I tried re-entering the formula from scratch and it still didn't work!

"David Biddulph" wrote:

The formulae seem to work OK here.

I suggest, to avoid any further transcription errors, that you copy
and
paste the formulae you have used directly from the conditional
formatting
formula bars to the group here.

If you are still struggling, it is also worth breaking down the
formula
an
element at a time. If you're convinced that you've got red showing
from
=AND(K2230,K223=TODAY(),K223-TODAY()<=5,$O223="") when you've got
28
Feb
07 in K223, then I suggest that you put a few intermediate steps in
various
cells, so you can look at:
=AND(K2230,K223=TODAY(),K223-TODAY()<=5,$O223="") and see whether
you
get
FALSE
=K223 and see whether you get 28/2/07 (or 39141 if you change format
to
General)
=(K223=TODAY()) and see whether you get FALSE
=TODAY() and see whether you get 30/5/07 (or 39232 as General)
etc.
--
David Biddulph

"singingsister" wrote in
message
...
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!