Conditional Formatting

[singingsister]

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!

 

[DazzaData]

A quick check reveals that the $o223 reference has a Zero not the letter o

apart from that I got it working fine

 

[singingsister]

thanks for that dazzadata. 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.

 

[DazzaData]

Hmm,

Are you sure that the formulae are looking at the right values i.e. value of
the cell it is in otherwise the shading may be out of step with the date.

The formula itself is fine so positioning might be the issue

 

[singingsister]

It needs to look from K3, L3 and M3 downwards with no end row as it's a
working spreadsheet. Does this help?

 

[DazzaData]

I would check the conditional formulae in k3,l3,m3 and check they are
looking at k3,l3,m3 & $O3, it may be that the rows in the conditional
formulae are out of step with the rows in the spreadsheet which would lead to
seemingly peculiar shading decisions

e.g. if the conditional was looking at row 4 in Row 3 it would shade row 3
according to the dates in row 4 and appear odd!

Failing that start again by pasting in the formula adjusting it from row 223
to row 3, fix the "zero" v "O" and it should work fine

 

[David Biddulph]

Please don't multipost. Cross-post if you must.

http://www.cs.tut.fi/~jkorpela/usenet/xpost.html

 

[singingsister]

I hate to tell you but I re-entered the formula from scratch and it still
didn't work!

 

[DazzaData]

i have

=AND(K3>0,K3>=TODAY(),K3-TODAY()<=5,$O3="") Format Red
=AND(K3>0,K3>=TODAY(),K3-TODAY()<=14,$O3="") Format Amber

For 30 May - Jun05 inclusive in K3 this shades K3 red
For 06 Jun - Jun13 inclusive in K3 this shades K3 amber

Am at a loss to explain why it doesn't work for you. Its not putting quotes
around it is it?

 

[bj]

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]

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]

=and(N223>0,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?)