Color conditionning time format

[djsouljah]

Hi There!

Need your help plz..

I have 3 columns that have data in time HH:MM:SS format

I would like to use color conditioning on them..but it just won't work no matter what I try...

For example if the amount is between 00:00:40 and 00:00:50 the color should turn red.

PS: how to we attach files to this forum so u can take a look at the file? or does it have to be external link ?

Thanks for your help.

 

[Marcelo Branco]

Maybe this

A
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64 align=right>00:00:39</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl65 height=20 align=right>00:00:40</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl65 height=20 align=right>00:00:42</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl65 height=20 align=right>00:00:44</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl65 height=20 align=right>00:00:48</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl65 height=20 align=right>00:00:50</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>00:00:52</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>00:00:54</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl65 height=20 align=right>00:00:46</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl65 height=20 align=right>00:00:41</TD></TR></TBODY></TABLE>

Home > Conditional Formatting > New Rule > Use a formula to...

enter this formula

=AND($A1>=40/86400,$A1<=50/86400)

pick Fill ---> Red

HTH

M.

 

[djsouljah]

Nope still doesnt work

heres the file..

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

Column E, F and H

its realy weird..

 

[asking]

Try ~

Say 1 column of your data with the times in it is A3 to A50
You would do this ~

Select the range first then click Use a formula... , click new, then type

=AND(SECOND(A3)>40,SECOND(A3)<50)

Now format, OK, Apply. Repeat for other columns

Cheers

 

[Marcelo Branco]

Select E8:F32

Home > CF > New Rule > Use a formula...
enter this
=AND(1*F8>=40/86400,1*F8<=50/86400)
Fill --> Red


Select H8:H32


Home > CF > New Rule > Use a formula...
enter this
=AND(1*H8>=40/86400,1*H8<=50/86400)
Fill --> Red


M.

 

[asking]

My previous post was not inclusive

You can change the formula to ~

=AND(SECOND(A3)>=40,SECOND(A3)<=50)

or

=AND(SECOND(A3)>39,SECOND(A3)<51)

 

[Marcelo Branco]

Sorry, there is a typo in my first formula

=AND(1*E8>=40/86400,1*E8<=50/86400)

is the correct

M.

 

[djsouljah]

Thanks guys its the colors are working now but i get an error when i try to lookup certain agents though..

and I verified to see if it was the conditionning that was causing it bu undo what i did..and the error dissaperes..

the error states: Circular reference warning

 

[asking]

For posting tables go to this thread

http://www.mrexcel.com/forum/showthread.php?t=397317

 

[djsouljah]

posting tables?..I don't understand what that thread has to do with my question lol...