Conditional Formatting Times

[Eraengineer]

I'm trying to format cells containing times to show in color what shift produced what parts based on the times.

I understand that excel works with times as decimals like this...
<TABLE style="WIDTH: 122pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=162><COLGROUP><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" span=2 width=81><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 61pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=21 width=81 align=right>12:00:00 AM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=81 align=right>0.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=17 align=right>1:00:00 AM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>0.04</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=17 align=right>2:00:00 AM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>0.08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=17 align=right>3:00:00 AM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>0.13</TD></TR></TBODY></TABLE>

How can I format times so that the cell changes color?
Example: third shift runs from 11:00pm-7:00am, first runs 7:00am-3:00pm, and second runs 3:00pm-11:00pm. Now the issue I am getting is that when the day changes I get this!

<TABLE style="WIDTH: 122pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=162><COLGROUP><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" span=2 width=81><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 61pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=17 width=81 align=right>11:00:00 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=81 align=right>0.96</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=17 align=right>12:00:00 AM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 align=right>1.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=17 align=right>1:00:00 AM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 align=right>1.04</TD></TR></TBODY></TABLE>

The 1.00 and 1.04 are not allowing me to use conditional formating! Just enter 12:00pm into a cell and drag it down for two days and try and format those shifts to three different colors.

 

[T. Valko]

Does the 5 in this formula relate to rounding because as I change the value it effects the outcome of the data?

=AND(MOD($E338,1)>=TIME(7,0,0),(MOD($E338,1)<=TIME(15,5,0)))

The 5 would be the time minutes.

The TIME( ) function takes these arguments:

TIME(Hour, Minute, Second)

So:

TIME(15,5,0) = 3:05 PM

Hour = 15
Minute = 5
Second = 0

 

[Rick Rothstein]

Rick and that does?

I had this recollection of reading that MOD(<a time value>,1) could have issues when used to compare agains values returned by the TIME function. I did a quick search and found what I think I remembered reading...

http://www.excelthreads.com/questions/61020/time-formula

Scroll down until you find the section that starts with this sentence...

"Test when A1 contains 1/1/2009 7:20 AM and 1/1/2009 11:20 PM"

I think the "equal" part of your "less than or equal" test may be missing some results it shouldn't (if I am reading the caution in the referenced discussion correctly)... comparing two TIME function results should guarantee there will be no problems in the resulting comparison.