Help With Conditional Formatting

[Guest]

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(k223>0,k223>=today(),k223-today()<=5,$0223="") and to flag amber i used
=and(k223>0,k223>=today(),k223-today()<=14,$0223="")

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

I'm using excel 03 and im in the UK. It's definately set to date. The
formula needs to look from K3, L3 and M3 downwards with no end row as it's a
working spreadsheet. Does this help?

 

[David Biddulph]

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(K223>0,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(K223>0,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.

 

[Guest]

Because you have k223>=today() in the formula you should not get any color
for FEb 28 2007. the only way you wuld get color was if the today function
was returning the wrong date. Put =today() in a cell and see what date is
returned. Your computer may be set at the wrong date.

 

[Guest]

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]

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?

 

[Guest]

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]

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.

 

[Guest]

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!

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

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]

If you can't take a copy of the document and vary parameters in it, then
we're not likely to be able to help you find your error.

The formulae were right, and work correctly for me and for other people that
are trying to help you, so you're obviously not doing what you tell us
you're doing, but you're not giving us enough information to find out what
you've done wrong. We've advised you on the steps to follow to sort out
where you've gone wrong, but if you can't do it systematically in that way,
then I think you're on you're own.
--
David Biddulph

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!

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

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

:

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?

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!

:

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(K223>0,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(K223>0,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.

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.

:

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

:

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(k223>0,k223>=today(),k223-today()<=5,$0223="") and to
flag
amber i
used
=and(k223>0,k223>=today(),k223-today()<=14,$0223="")

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