Condtional Format Issue

[Toonies]

Hi and thank you for looking at my post

The issue that I have is the following

I am using the following Conditional Formatting Formula

=AND(LEN($C9)>0,($C9<=E$7)+($D9>E$7+($C9>$D9)=2)

I am encountering the following problem

C9 = Start Time
D9 = End Time

when the time goes over 24 hours ie: 22:00 to 7:00

They fill correctly for that specific Time range

However they also fill for the hours 6:00 to 7:00 on previous day when they should show blank.

ie 6:00 to 7:00 plus 22:00 to 7:00

I only need them to fill 22:00 to 7:00

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 6px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 6px"><COL style="WIDTH: 101px"><COL style="WIDTH: 101px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 53px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 18pt" rowSpan=3>Start</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 18pt; FONT-WEIGHT: bold" rowSpan=3>Finish</TD><TD style="FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">06:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">06:30</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 8pt"> </TD><TD style="FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 11pt">To</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 11pt">To</TD></TR><TR style="HEIGHT: 53px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">06:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">06:30</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">07:00</TD></TR><TR style="HEIGHT: 6px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 80px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-SIZE: 15pt"> </TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 20pt; FONT-WEIGHT: bold">22:00</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 20pt; FONT-WEIGHT: bold">7:00</TD><TD> </TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 28pt; FONT-WEIGHT: bold"> </TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 28pt; FONT-WEIGHT: bold"> </TD></TR></TBODY></TABLE>

OS Windows Vista - Excel 2003

 

[Toonies]

Hi does anyone have any ideas on how to fix this as i'm at a loss the formula was suggested by
barry houdini

MrExcel MVP


Who has helped out many a time

 

[Walky]

Hi,

Your formula is missing a parenthesis...

Once corrected, try using it directly in a cell to check the result before using it as a conditional formatting condition.

Rgds,

W.

 

[Toonies]

Hi thanks for pointing that out

here is the Formula

=AND(LEN($C9)>0,($C9<=E$7)+($D9>E$7)+($C9>$D9)=2)

 

[Walky]

Ok,

So if you want your condition to be "TRUE" when both formulas included in the AND function equal 2, then the parenthesis should be closed before the "=2".

If it is not the case, then something is missing in your formula, because it has to give a "TRUE" (1) or "FALSE" (0) result.

Try to be more explicit on what the formula is supposed to check/calculate.

W.

 

[Toonies]

Hi the formula is basically acting as a Gantt Time bar Chart

The time in row 5 & 7 are set out in 1/2 hr intervals from 6 am to 6am the next day

So when you enter the Start and Finish times in only that specific time period should be highlighted it works 95% apart from when the time pass over midnight

ie: 22:00 to 7:00

They fill correctly for that specific Time range

However they also fill for the hours 6:00 to 7:00 on previous day when they should show blank.

ie 6:00 to 7:00 plus 22:00 to 7:00

I only need them to fill 22:00 to 7:00


I have just altered the formula to this

=AND(LEN($C9)>0,($C9<=E$7)+($D9>E$7)+($C9>$D9)=2)*($D9<>"")

I use the following Conditional Formatting Formulas in

C9 (down)

Condition 1 =SUMPRODUCT(COUNTIF((C9,"*"&Task&"*"))>0    
Condition 2 =AND(C9<>"",D9="")

D9 (down)

Condition 1 =SUMPRODUCT(COUNTIF((C9,"*"&Task&"*"))>0      Condition 2 =AND(D9<>"",C9="")

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 215px"><COL style="WIDTH: 6px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 6px"><COL style="WIDTH: 101px"><COL style="WIDTH: 101px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 53px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 24pt" rowSpan=3></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 18pt" rowSpan=3>Start</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 18pt; FONT-WEIGHT: bold" rowSpan=3>Finish</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">06:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">06:30</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 11pt">To</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 11pt">To</TD></TR><TR style="HEIGHT: 53px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">06:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">06:30</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">07:00</TD></TR><TR style="HEIGHT: 6px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 80px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt; FONT-WEIGHT: bold">NEED F9 to AO9 to be blank if C9 filled with Time and D9 is Empty</TD><TD style="FONT-SIZE: 15pt"></TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 20pt; FONT-WEIGHT: bold">22:00</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 20pt; FONT-WEIGHT: bold">7:00</TD><TD></TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 28pt; FONT-WEIGHT: bold"></TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 28pt; FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 80px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 22pt; FONT-WEIGHT: bold">Incorrect </TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 20pt; FONT-WEIGHT: bold">HOL</TD><TD style="TEXT-ALIGN: right; COLOR: #ffffff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">7:00</TD><TD></TD><TD style="BACKGROUND-COLOR: #99ccff; COLOR: #ffffff; FONT-SIZE: 28pt; FONT-WEIGHT: bold"></TD><TD style="BACKGROUND-COLOR: #99ccff; COLOR: #ffffff; FONT-SIZE: 28pt; FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 80px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 22pt; FONT-WEIGHT: bold">Returns Correct Result</TD><TD></TD><TD style="COLOR: #ffffff; FONT-SIZE: 20pt; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-SIZE: 20pt; FONT-WEIGHT: bold">7:00</TD><TD></TD><TD style="FONT-SIZE: 28pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 28pt; FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 80px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 22pt; FONT-WEIGHT: bold">Returns Correct Result</TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-SIZE: 20pt; FONT-WEIGHT: bold">6:00</TD><TD style="FONT-SIZE: 20pt; FONT-WEIGHT: bold"></TD><TD></TD><TD style="FONT-SIZE: 28pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 28pt; FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 80px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 22pt; FONT-WEIGHT: bold">Returns Correct Result</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 20pt; FONT-WEIGHT: bold">6:00</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 20pt; FONT-WEIGHT: bold">7:00</TD><TD></TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 28pt; FONT-WEIGHT: bold"></TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 28pt; FONT-WEIGHT: bold"></TD></TR></TBODY></TABLE>

 

[Toonies]

The conditional formatting in cells C9 and D9 do the following

when text from a named range entered in

C9 it highlights the cell yellow and any text value in D9 turns white

when text is entered C9 with no text in D9 the C9 is highlighted red

when text is entered D9 with no text in C9 the D9 is highlighted red

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 215px"><COL style="WIDTH: 6px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 6px"><COL style="WIDTH: 101px"><COL style="WIDTH: 101px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 53px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 24pt" rowSpan=3> </TD><TD style="FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 18pt" rowSpan=3>Start</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 18pt; FONT-WEIGHT: bold" rowSpan=3>Finish</TD><TD style="FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">06:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">06:30</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 8pt"> </TD><TD style="FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 11pt">To</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 11pt">To</TD></TR><TR style="HEIGHT: 53px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">06:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">06:30</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">07:00</TD></TR><TR style="HEIGHT: 6px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 80px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 12pt; FONT-WEIGHT: bold">NEED F9 to G9 to be blank as that is Time range from today</TD><TD style="FONT-SIZE: 15pt"> </TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 20pt; FONT-WEIGHT: bold">22:00</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 20pt; FONT-WEIGHT: bold">7:00</TD><TD> </TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 28pt; FONT-WEIGHT: bold"> </TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 28pt; FONT-WEIGHT: bold"> </TD></TR><TR style="HEIGHT: 80px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 22pt; FONT-WEIGHT: bold">Incorrect </TD><TD> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 20pt; FONT-WEIGHT: bold">HOL</TD><TD style="TEXT-ALIGN: right; COLOR: #ffffff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">7:00</TD><TD> </TD><TD style="BACKGROUND-COLOR: #99ccff; COLOR: #ffffff; FONT-SIZE: 28pt; FONT-WEIGHT: bold"> </TD><TD style="BACKGROUND-COLOR: #99ccff; COLOR: #ffffff; FONT-SIZE: 28pt; FONT-WEIGHT: bold"> </TD></TR><TR style="HEIGHT: 80px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 22pt; FONT-WEIGHT: bold">Returns Correct Result</TD><TD> </TD><TD style="COLOR: #ffffff; FONT-SIZE: 20pt; FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-SIZE: 20pt; FONT-WEIGHT: bold">7:00</TD><TD> </TD><TD style="FONT-SIZE: 28pt; FONT-WEIGHT: bold"> </TD><TD style="FONT-SIZE: 28pt; FONT-WEIGHT: bold"> </TD></TR><TR style="HEIGHT: 80px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 22pt; FONT-WEIGHT: bold">Returns Correct Result</TD><TD> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-SIZE: 20pt; FONT-WEIGHT: bold">6:00</TD><TD style="FONT-SIZE: 20pt; FONT-WEIGHT: bold"> </TD><TD> </TD><TD style="FONT-SIZE: 28pt; FONT-WEIGHT: bold"> </TD><TD style="FONT-SIZE: 28pt; FONT-WEIGHT: bold"> </TD></TR><TR style="HEIGHT: 80px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 22pt; FONT-WEIGHT: bold">Returns Correct Result</TD><TD> </TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 20pt; FONT-WEIGHT: bold">6:00</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 20pt; FONT-WEIGHT: bold">7:00</TD><TD> </TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 28pt; FONT-WEIGHT: bold"> </TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 28pt; FONT-WEIGHT: bold"> </TD></TR></TBODY></TABLE>

 

[Toonies]

I am sure that the problem is caused by this part of the code

($C9>$D9)

 

[Toonies]

Here is a link if you wish to view the file

http://www.mediafire.com/?c3j0dze2eih4qya

 

[Walky]

Hi,

After going over your examples several times, I think I get part of what you need for the conditional formating of columns F and over.

Try changing your formula to include something like:

=NOT(OR(ISBLANK(C9);ISBLANK(D9)))

in order for the conditional format not to apply when either C9 or D9 is blank.

For the other formulas, I'll have to go through your posts again to try to understand what you need.

Just to be sure, please confirm that you have 2 different sets of conditional formats you are trying to sort out:

- one for columns F and over
- one for columns C and D

Furthermore, I don't understand where the result of the time difference between C9 and D9 is supposed to show (or is it only used for conditional formating).

To finish-off, what is the use of the time in cell E7?

By the way, I'm at work and cannot access the link you provided...

Rgds,

W.