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 thanks for taking the time to look at this problem

The Conditional Formatting in C and D work

The real issue is the Conditional Formatting of F and over but only when the time has crossed over midnight.

example

22:00 to 7:00 should only highlight the cells for that specfic time period

but it is also highlighting the cells 6:00 to 7:00 pre midnight.

I am considering dropping the time vba and using data validation with a time format of h:mm AM/PM which I think should solve the conditional formatting formula coping with the time period.

I am open to any suggestions that you may have.

Many thanks

Toonies

 

[Walky]

Hi,

After taking a look at your worksheet, I hope I have finally understood what you need:

For a period ranging from 22:00 to 7:00, what needs to be highlighted is the the period between 22:00 and 24:00...

You mention "that specific time period" needs to be highlighted and that is what is currently done, since there is no notion of date! So excel cannot guess that the 6:00 to 7:00 period is on another day and should not show at the begining of your table, ie. before the period which starts at 22:00.

If your layout does not span onto a second day, then, as I mentioned higher up, only the time between 22:00 and 24:00 needs to be taken into account.

To acheive that, you need to use something like:

IF(C9>D9;24:00-C9;C9-D9)

which will calculate the time between the start time and midnight for periods starting on one day and finishing on another, and calculate the time between start and finish for intra-day periods.

I hope I've got it right! My imagination has reached its limits based on the explanation you provide. And if I'm right, it could have been solved much faster by pointing out that anything that goes over midnight should not be highlighted on your 6:00 to 24:00 timetable...

Regards,

W.

 

[Walky]

I hope I don't seem too frustrated, because it's a pity I or others couldn't help you out much faster.

W.