Conditional formatting strangeness

C

christopherp

Hi Guys

I have setup up a formula to work out out how many days there ar
between today's date (continually updating using today() function) an
the due date for my assignments.

The formula works fine and the result within the cell is appended wit
"days" at the end. Once there are 0 days left "complete" will b
dispalyed in the cell.

No problems there, formula is pasted below:

=IF(('Assessment schedule'!J4-TODAY())<=0,"Complete",'Assessmen
schedule'!J4-TODAY()&" days")

The strangeness starts when I try to apply conditional formatting t
these cells.

I applied a conditional format to all cells with a value of less tha
30 days which works fine until the value (number of days) drops to les
than less than 10 days!! :confused:

Between 30 days and 10 days the conditional formatting is applied. a
soon as the number of days reaches 10 each cell reverts back to defaul
formatting.

I have even tried to apply an additional condition which should appl
formatting to cells with a value equal to or less than 10 days but thi
does not work either.

I can change the due date of for each assignment in the assignmen
schedule worksheet so the value is between 10 and 30 days and th
formatting works fine but as soon as that date is changed to return
value of less than 10 days it reverts back to default formatting.

I am stumped :(

any help you guys can offer would be greatly appreciated

Cheers

Chri
 
G

Guest

Hi

Without seeing the formulas you are using for your CF, I would guess that
your conditions may be in the wrong order.
For example, if your conditions are for 'less than' calculations, make sure
you use the smallest value first.
<10 condition first, then <20, then <30 in that order

Hope this helps.
Andy.

"christopherp" <[email protected]>
wrote in message
news:[email protected]...
 
B

Bob Phillips

You don't say what you have in the CF formulae. You do know that you can
only refer to another sheet in CF by using range names?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"christopherp" <[email protected]>
wrote in message
news:[email protected]...
 
C

christopherp

Hey guys thanks for responding.

I have attached a screen shot so you can see exactly what is going on.

I don't want to change the CF between 10 and 30days, all I want to do
is CF all the cells in the same manner once the due date is within the
thirty day threshold.

All works fine until the difference between the due date and today() is
less than 10 days at which stage it reverts back to default format.

See cells c13 and c19 for an example.

All cells in the "C" column are CF'ed the same.

Hope this helps us solve the mystery

Chris


+-------------------------------------------------------------------+
|Filename: conditional formatting.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4420 |
+-------------------------------------------------------------------+
 
C

christopherp

I have gone through and removed the & "days" at the end of the formula
in all the cells in "C" column and the CF works as expected but as soon
as that is there it stops working below 10 days...

I am still stumped
 
J

JudithJubilee

Hello christopherp,

I've been playing with this and just cannot get it to work with the Days in
anywhere.

Might I suggest a slightly different approach.

=IF(('Assessment schedule'!J4-TODAY())<=0,0,'Assessment
schedule'!J4-TODAY)

This will give you either a 0 or a positive number.

You can then custom number format the cells to have "days" added if a
positive and "Complete" if 0

Format + Cells + Custom and type the following

0" days";0;"Complete"

You can then put Conditional formatting:
<=30 - one colour
=Complete - another colour

This does work on mine.

Judith
 
G

Guest

Hi

I reckon it's because you use " days" in your cell - which makes it text
rather than value.
To Excel, "9 days" is more than "30 days" - so your formula falls down. You
would be better off putting the actual number of days in there 30 and
formatting the cell with a custom format #" days".

Hope this helps.
Andy.

"christopherp" <[email protected]>
wrote in message
news:[email protected]...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional formatting with dates 7
Conditional formatting with two columns 0
Conditional Formatting in Excel 2003 2
2007 conditional formatting not playing ball 5
Conditional Formating based on Date 5
conditional formatting date and blank cell 4
Conditional formatting with formula 3
Conditional formatting 1

Top