software

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
quot;daysquot; at the end. Once there are 0 days left quot;completequot; will be
dispalyed in the cell.

No problems there, formula is pasted below:

=IF(('Assessment schedule'!J4-TODAY())lt;=0,quot;Completequot;,'Assessment
schedule'!J4-TODAY()amp;quot; daysquot;)

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

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: www.excelforum.com/member.php...foamp;userid=4162
View this thread: www.excelforum.com/showthread...hreadid=519266Hi

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.
lt;10 condition first, then lt;20, then lt;30 in that order

Hope this helps.
Andy.

quot;christopherpquot; gt;
wrote in message
news:christopherp.248sty_1141639200.9315@excelforu m-nospam.com...
gt;
gt; Hi Guys
gt;
gt; I have setup up a formula to work out out how many days there are
gt; between today's date (continually updating using today() function) and
gt; the due date for my assignments.
gt;
gt; The formula works fine and the result within the cell is appended with
gt; quot;daysquot; at the end. Once there are 0 days left quot;completequot; will be
gt; dispalyed in the cell.
gt;
gt; No problems there, formula is pasted below:
gt;
gt; =IF(('Assessment schedule'!J4-TODAY())lt;=0,quot;Completequot;,'Assessment
gt; schedule'!J4-TODAY()amp;quot; daysquot;)
gt;
gt; The strangeness starts when I try to apply conditional formatting to
gt; these cells.
gt;
gt; I applied a conditional format to all cells with a value of less than
gt; 30 days which works fine until the value (number of days) drops to less
gt; than less than 10 days!!
gt;
gt; Between 30 days and 10 days the conditional formatting is applied. as
gt; soon as the number of days reaches 10 each cell reverts back to default
gt; formatting.
gt;
gt; I have even tried to apply an additional condition which should apply
gt; formatting to cells with a value equal to or less than 10 days but this
gt; does not work either.
gt;
gt; I can change the due date of for each assignment in the assignment
gt; schedule worksheet so the value is between 10 and 30 days and the
gt; formatting works fine but as soon as that date is changed to return a
gt; value of less than 10 days it reverts back to default formatting.
gt;
gt; I am stumped
gt;
gt; any help you guys can offer would be greatly appreciated
gt;
gt; Cheers
gt;
gt; Chris
gt;
gt;
gt; --
gt; christopherp
gt; ------------------------------------------------------------------------
gt; christopherp's Profile:
gt; www.excelforum.com/member.php...foamp;userid=4162
gt; View this thread: www.excelforum.com/showthread...hreadid=519266
gt;
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)

quot;christopherpquot; gt;
wrote in message
news:christopherp.248sty_1141639200.9315@excelforu m-nospam.com...
gt;
gt; Hi Guys
gt;
gt; I have setup up a formula to work out out how many days there are
gt; between today's date (continually updating using today() function) and
gt; the due date for my assignments.
gt;
gt; The formula works fine and the result within the cell is appended with
gt; quot;daysquot; at the end. Once there are 0 days left quot;completequot; will be
gt; dispalyed in the cell.
gt;
gt; No problems there, formula is pasted below:
gt;
gt; =IF(('Assessment schedule'!J4-TODAY())lt;=0,quot;Completequot;,'Assessment
gt; schedule'!J4-TODAY()amp;quot; daysquot;)
gt;
gt; The strangeness starts when I try to apply conditional formatting to
gt; these cells.
gt;
gt; I applied a conditional format to all cells with a value of less than
gt; 30 days which works fine until the value (number of days) drops to less
gt; than less than 10 days!!
gt;
gt; Between 30 days and 10 days the conditional formatting is applied. as
gt; soon as the number of days reaches 10 each cell reverts back to default
gt; formatting.
gt;
gt; I have even tried to apply an additional condition which should apply
gt; formatting to cells with a value equal to or less than 10 days but this
gt; does not work either.
gt;
gt; I can change the due date of for each assignment in the assignment
gt; schedule worksheet so the value is between 10 and 30 days and the
gt; formatting works fine but as soon as that date is changed to return a
gt; value of less than 10 days it reverts back to default formatting.
gt;
gt; I am stumped
gt;
gt; any help you guys can offer would be greatly appreciated
gt;
gt; Cheers
gt;
gt; Chris
gt;
gt;
gt; --
gt; christopherp
gt; ------------------------------------------------------------------------
gt; christopherp's Profile:
www.excelforum.com/member.php...foamp;userid=4162
gt; View this thread: www.excelforum.com/showthread...hreadid=519266
gt;

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 quot;Cquot; column are CF'ed the same.

Hope this helps us solve the mystery

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

--
christopherp
------------------------------------------------------------------------
christopherp's Profile: www.excelforum.com/member.php...foamp;userid=4162
View this thread: www.excelforum.com/showthread...hreadid=519266
I have gone through and removed the amp; quot;daysquot; at the end of the formula
in all the cells in quot;Cquot; 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: www.excelforum.com/member.php...foamp;userid=4162
View this thread: www.excelforum.com/showthread...hreadid=519266Hi

That URL doesn't work for me.

Andy.

quot;christopherpquot; gt;
wrote in message
news:christopherp.248vdn_1141642500.8698@excelforu m-nospam.com...
gt;
gt; Hey guys thanks for responding.
gt;
gt; I have attached a screen shot so you can see exactly what is going on.
gt;
gt; I don't want to change the CF between 10 and 30days, all I want to do
gt; is CF all the cells in the same manner once the due date is within the
gt; thirty day threshold.
gt;
gt; All works fine until the difference between the due date and today() is
gt; less than 10 days at which stage it reverts back to default format.
gt;
gt; See cells c13 and c19 for an example.
gt;
gt; All cells in the quot;Cquot; column are CF'ed the same.
gt;
gt; Hope this helps us solve the mystery
gt;
gt; Chris
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: conditional formatting.jpg |
gt; |Download: www.excelforum.com/attachment.php?postid=4420 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; christopherp
gt; ------------------------------------------------------------------------
gt; christopherp's Profile:
gt; www.excelforum.com/member.php...foamp;userid=4162
gt; View this thread: www.excelforum.com/showthread...hreadid=519266
gt;
Try testing against 30, not =quot;30quot;

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;christopherpquot; gt;
wrote in message
news:christopherp.248vdn_1141642500.8698@excelforu m-nospam.com...
gt;
gt; Hey guys thanks for responding.
gt;
gt; I have attached a screen shot so you can see exactly what is going on.
gt;
gt; I don't want to change the CF between 10 and 30days, all I want to do
gt; is CF all the cells in the same manner once the due date is within the
gt; thirty day threshold.
gt;
gt; All works fine until the difference between the due date and today() is
gt; less than 10 days at which stage it reverts back to default format.
gt;
gt; See cells c13 and c19 for an example.
gt;
gt; All cells in the quot;Cquot; column are CF'ed the same.
gt;
gt; Hope this helps us solve the mystery
gt;
gt; Chris
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: conditional formatting.jpg |
gt; |Download: www.excelforum.com/attachment.php?postid=4420 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; christopherp
gt; ------------------------------------------------------------------------
gt; christopherp's Profile:
www.excelforum.com/member.php...foamp;userid=4162
gt; View this thread: www.excelforum.com/showthread...hreadid=519266
gt;

www.excelforum.com/attachment...0amp;d=1141642098--
christopherp
------------------------------------------------------------------------
christopherp's Profile: www.excelforum.com/member.php...foamp;userid=4162
View this thread: www.excelforum.com/showthread...hreadid=519266Hello 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())lt;=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 quot;daysquot; added if a
positive and quot;Completequot; if 0

Format Cells Custom and type the following

0quot; daysquot;;0;quot;Completequot;

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

This does work on mine.

Judith
--
Hope this helpsquot;christopherpquot; wrote:

gt;
gt; www.excelforum.com/attachment...0amp;d=1141642098
gt;
gt;
gt; --
gt; christopherp
gt; ------------------------------------------------------------------------
gt; christopherp's Profile: www.excelforum.com/member.php...foamp;userid=4162
gt; View this thread: www.excelforum.com/showthread...hreadid=519266
gt;
gt;


Excel automtcally adds the =(equals sign and the quot;XXXXXquot; (quotation
marks)--
christopherp
------------------------------------------------------------------------
christopherp's Profile: www.excelforum.com/member.php...foamp;userid=4162
View this thread: www.excelforum.com/showthread...hreadid=519266