Conditional formatting strangeness
 

Hi Guys

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

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

No problems there, formula is pasted below:

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

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

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

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

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

I can change the due date of for each assignment in the assignment
schedule worksheet so the value is between 10 and 30 days and the
formatting works fine but as soon as that date is changed to return a
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

Chris


--
christopherp
------------------------------------------------------------------------
christopherp's Profile: http://www.excelforum.com/member.php...fo&userid=4162
View this thread: http://www.excelforum.com/showthread...hreadid=519266

Conditional formatting strangeness
 

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"
wrote in message
news:christopherp.248sty_1141639200.9315@excelforu m-nospam.com...

Hi Guys

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

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

No problems there, formula is pasted below:

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

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

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

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

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

I can change the due date of for each assignment in the assignment
schedule worksheet so the value is between 10 and 30 days and the
formatting works fine but as soon as that date is changed to return a
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

Chris


--
christopherp
------------------------------------------------------------------------
christopherp's Profile:
http://www.excelforum.com/member.php...fo&userid=4162
View this thread: http://www.excelforum.com/showthread...hreadid=519266

Conditional formatting strangeness
 

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"
wrote in message
news:christopherp.248sty_1141639200.9315@excelforu m-nospam.com...

Hi Guys

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

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

No problems there, formula is pasted below:

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

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

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

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

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

I can change the due date of for each assignment in the assignment
schedule worksheet so the value is between 10 and 30 days and the
formatting works fine but as soon as that date is changed to return a
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

Chris


--
christopherp
------------------------------------------------------------------------
christopherp's Profile:
http://www.excelforum.com/member.php...fo&userid=4162
View this thread: http://www.excelforum.com/showthread...hreadid=519266

Conditional formatting strangeness
 

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 |
+-------------------------------------------------------------------+

--
christopherp
------------------------------------------------------------------------
christopherp's Profile: http://www.excelforum.com/member.php...fo&userid=4162
View this thread: http://www.excelforum.com/showthread...hreadid=519266

Conditional formatting strangeness
 

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


--
christopherp
------------------------------------------------------------------------
christopherp's Profile: http://www.excelforum.com/member.php...fo&userid=4162
View this thread: http://www.excelforum.com/showthread...hreadid=519266

Conditional formatting strangeness
 

Hi

That URL doesn't work for me.

Andy.

"christopherp"
wrote in message
news:christopherp.248vdn_1141642500.8698@excelforu m-nospam.com...

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 |
+-------------------------------------------------------------------+

--
christopherp
------------------------------------------------------------------------
christopherp's Profile:
http://www.excelforum.com/member.php...fo&userid=4162
View this thread: http://www.excelforum.com/showthread...hreadid=519266

Conditional formatting strangeness
 

Try testing against 30, not ="30"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"christopherp"
wrote in message
news:christopherp.248vdn_1141642500.8698@excelforu m-nospam.com...

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 |
+-------------------------------------------------------------------+

--
christopherp
------------------------------------------------------------------------
christopherp's Profile:
http://www.excelforum.com/member.php...fo&userid=4162
View this thread: http://www.excelforum.com/showthread...hreadid=519266

Conditional formatting strangeness
 

http://www.excelforum.com/attachment...0&d=1141642098


--
christopherp
------------------------------------------------------------------------
christopherp's Profile: http://www.excelforum.com/member.php...fo&userid=4162
View this thread: http://www.excelforum.com/showthread...hreadid=519266

Conditional formatting strangeness
 

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
--
Hope this helps


"christopherp" wrote:


http://www.excelforum.com/attachment...0&d=1141642098


--
christopherp
------------------------------------------------------------------------
christopherp's Profile: http://www.excelforum.com/member.php...fo&userid=4162
View this thread: http://www.excelforum.com/showthread...hreadid=519266

Conditional formatting strangeness
 

Excel automtcally adds the =(equals sign and the "XXXXX" (quotation
marks)


--
christopherp
------------------------------------------------------------------------
christopherp's Profile: http://www.excelforum.com/member.php...fo&userid=4162
View this thread: http://www.excelforum.com/showthread...hreadid=519266

Conditional formatting strangeness
 

Fantastic Judith that works a treat - thank you :) :)


JudithJubilee Wrote:
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
--
Hope this helps


"christopherp" wrote:



http://www.excelforum.com/attachment...0&d=1141642098


--
christopherp

------------------------------------------------------------------------
christopherp's Profile:
http://www.excelforum.com/member.php...fo&userid=4162
View this thread:
http://www.excelforum.com/showthread...hreadid=519266




--
christopherp
------------------------------------------------------------------------
christopherp's Profile: http://www.excelforum.com/member.php...fo&userid=4162
View this thread: http://www.excelforum.com/showthread...hreadid=519266

Conditional formatting strangeness
 

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"
wrote in message
news:christopherp.248y5n_1141646102.0136@excelforu m-nospam.com...

Excel automtcally adds the =(equals sign and the "XXXXX" (quotation
marks)


--
christopherp
------------------------------------------------------------------------
christopherp's Profile:
http://www.excelforum.com/member.php...fo&userid=4162
View this thread: http://www.excelforum.com/showthread...hreadid=519266

Conditional formatting strangeness
 

No problem. That was a bit tricky!! Something to get the teeth into!
--
Hope this helps


"christopherp" wrote:


Fantastic Judith that works a treat - thank you :) :)


JudithJubilee Wrote:
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
--
Hope this helps


"christopherp" wrote:



http://www.excelforum.com/attachment...0&d=1141642098


--
christopherp

------------------------------------------------------------------------
christopherp's Profile:
http://www.excelforum.com/member.php...fo&userid=4162
View this thread:
http://www.excelforum.com/showthread...hreadid=519266




--
christopherp
------------------------------------------------------------------------
christopherp's Profile: http://www.excelforum.com/member.php...fo&userid=4162
View this thread: http://www.excelforum.com/showthread...hreadid=519266

Conditional formatting strangeness
 

It didn't for me assuming that you don't append days as you said you didn't
now.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"christopherp"
wrote in message
news:christopherp.248y5n_1141646102.0136@excelforu m-nospam.com...

Excel automtcally adds the =(equals sign and the "XXXXX" (quotation
marks)


--
christopherp
------------------------------------------------------------------------
christopherp's Profile:
http://www.excelforum.com/member.php...fo&userid=4162
View this thread: http://www.excelforum.com/showthread...hreadid=519266