Conditional Format Question when Time crosses over into next day

[Toonies]

Hi and thanks for taking the time to look at my question which is as follows

I am using the following CF formula

Condition 1 - colours a cell within a certain time frame

=AND(LEN($C9)>0,$C9<=E$7,$D9>E$7,SUMPRODUCT(COUNTIF(F9,"*"Task"*"))>0)+AND(LEN($C9)>0,$C9<=E$7,$D9>E$7)

it works great for time within that day

ie: 12:00 to 19:00

but if the time crosses over into the next day the cells within the time range don't change colour.

ie: 22:00 to 06: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">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>

I look forward to any help or replies

OS Windows Vista Excel 2003

Many thanks

Toonies

 

[barry houdini]

I'm not sure why you have some of the conditions twice.....but you should be able to accommodate time periods crossing midnight by changing this part

$C9<=E$7,$D9>E$7

to

($C9<=E$7)+($D9>E$7)+($C9>$D9)=2

 

[Toonies]

Barry your the "MAN " yet again.

it work perfectly

Many Thanks

Toonies

 

[Toonies]

Hi Barry,

your formula works great

=AND(LEN($C9)>0,($C9<=E$7)+($D9>E$7+($C9>$D9)=2,SUMPRODUCT(F9,"*"&Task&"*"))>0,($C9)>0,($C9<=E$7)+($D9>E$7+($C9>$D9)=2) - copied down

however I have one small glitch that I cannot overcome

It works fine if C9 and D10 are both filled.

It works fine if C9 is empty and D9 is filled then F9 stays blank (which is what I want)

But if C10 is filled and D10 is empty then F10 is filled (which I don't want)

<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="FONT-SIZE: 20pt"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-SIZE: 20pt; FONT-WEIGHT: bold">6: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">10</TD><TD> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-SIZE: 20pt; FONT-WEIGHT: bold">06:00</TD><TD style="FONT-SIZE: 20pt; FONT-WEIGHT: bold"> </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>


If its best to start a new thread then let me know and I will.

Many thanks

Toonies

 

[Toonies]

I have posted this at

http://www.excelforum.com/excel-general/775144-conditional-format-time-formula-need-help-please.html

 

[Toonies]

I have a copy of the file if you wish to look at it just download it.

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

I look forward to any suggestions on how to sort my problem

Toonies

 

[T. Valko]

I have a copy of the file if you wish to look at it just download it.

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

I look forward to any suggestions on how to sort my problem

Toonies

I have your file in front of me.

It looks like you're trying to make some sort of Gantt chart?

I can't tell what you're formula is doing just from looking at it.

However, it seems that all you should need to do is add another condition to the AND(...) function:

D9<>""

With that said, I can't figure out what this supposed to be doing:

SUMPRODUCT(COUNTIF(F9,"*"&Tasks&"*"))>0

F9 contains a drop down and the selections are the numbers 1 to 10. Tasks is a named range that contains a blank cell and the numbers 1 to 10. What are the wildcards for?

 

[Toonies]

Hi thank you for your rely

Where would you suggest I add the D9<>""

The drop downmenu is basically Department No's I'm looking to control the Font size via a VBA throught TEXT string as I will be putting in additional descriptions in the dropdown menu its sort of a work around Warp Text and Shrink to fit.

heres the coding for reference - well it does the job don't know if it can be refined but I would have to start a new thread to refine it. as there are other sheets in the main file.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Select Case Sh.Name
Case Is = "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", "SATURDAY", "SUNDAY", "MONDAY"
Set Rng = Worksheets("Times").Range("Task")
Dim rCell As Range

If Union(Target, Range("F9:AO39")).Address = _
Range("F9:AO39").Address Then
Application.EnableEvents = False
For Each rCell In Target
If Len(rCell.Text) < 4 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 28
ElseIf Len(rCell.Text) = 4 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 24
ElseIf Len(rCell.Text) = 5 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 26
ElseIf Len(rCell.Text) = 6 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 17
ElseIf Len(rCell.Text) = 7 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 19
ElseIf Len(rCell.Text) = 8 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 17
ElseIf Len(rCell.Text) = 9 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 17
ElseIf Len(rCell.Text) = 10 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 17
ElseIf Len(rCell.Text) = 11 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 17
ElseIf Len(rCell.Text) = 12 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 13.5
ElseIf Len(rCell.Text) = 13 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 13.5
ElseIf Len(rCell.Text) = 14 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 13.5
ElseIf Len(rCell.Text) = 15 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 12.5
ElseIf Len(rCell.Text) = 16 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 13.5
ElseIf Len(rCell.Text) = 17 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 11.5
ElseIf Len(rCell.Text) = 18 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 13.5
ElseIf Len(rCell.Text) = 19 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 12
ElseIf Len(rCell.Text) = 20 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 12
ElseIf Len(rCell.Text) = 21 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 12
ElseIf Len(rCell.Text) = 22 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 12
ElseIf Len(rCell.Text) = 23 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 11
ElseIf Len(rCell.Text) = 24 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 12
ElseIf Len(rCell.Text) = 25 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 12
ElseIf Len(rCell.Text) = 26 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 11
ElseIf Len(rCell.Text) = 27 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 11
Else
rCell.Font.Name = "Arial"
rCell.Font.Size = 13.5
End If
Next
Application.EnableEvents = True
End If
End Select
End Sub

 

[T. Valko]

Hi thank you for your rely

Where would you suggest I add the D9<>""

Well, now that I think about it you can just put it at the very end of the formula like this:

=Formula*(D9<>"")

I'm not much of a programmer so you'll get better help from someone else wrt your code.

 

[Toonies]

Hi thanks for the suggestion however I'm still at a loss