if function, please help

[yncip]

hey all i have some problem working with if function

for example i have this set of data with "time" Type
[TABLE="width: 100"]
<tbody>[TR]
[TD]6:00[/TD]
[/TR]
[TR]
[TD]15:31[/TD]
[/TR]
[TR]
[TD]22:50[/TD]
[/TR]
[TR]
[TD]01:45[/TD]
[/TR]
</tbody>[/TABLE]

What i want to do is to categorize those time into 3 categories, which is
CASE 1 = for range time 06:00 to 14:00
CASE 2 = for range time 14:00 to 22:00
CASE 3 = for range time 22:00 to 06:00

I've tried working with below function:
=IF(AND(A2>06:00,G2<14:00),"CASE 1",IF(AND(G2>14:00,G2<22:00),"CASE 2","CASE 3")) but that doesn't work.

please help

 

[mfexcel]

you may use vlookup for this kind of problem; suggest you to construct a table to hold the criteria for different range time instead of hard-coding it in the formula. Think about flexibility in modification in the future.


[TABLE="width: 360"]
<colgroup><col width="72" span="5" style="width:54pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 72"]Data[/TD]
[TD="class: xl65, width: 72"]Result[/TD]
[TD="class: xl63, width: 72"][/TD]
[TD="class: xl65, width: 144, colspan: 2"]Table[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 72"]06:00[/TD]
[TD="class: xl67"]CASE 1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl66"]00:00[/TD]
[TD="class: xl65"]CASE 3[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 72"]15:31[/TD]
[TD="class: xl67"]CASE 2[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl66"]06:00[/TD]
[TD="class: xl65"]CASE 1[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 72"]22:50[/TD]
[TD="class: xl67"]CASE 3[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl66"]14:00[/TD]
[TD="class: xl65"]CASE 2[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 72"]01:45[/TD]
[TD="class: xl67"]CASE 3[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl66"]22:00[/TD]
[TD="class: xl65"]CASE 3[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 72"]23:55[/TD]
[TD="class: xl67"]CASE 3[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]


As an example,
In B2
=VLOOKUP(A2,$D$2:$E$5,2)
copy down.
note: the time in "Table" must be in ascending order.

hey all i have some problem working with if function

for example i have this set of data with "time" Type
[TABLE="width: 100"]
<tbody>[TR]
[TD]6:00[/TD]
[/TR]
[TR]
[TD]15:31[/TD]
[/TR]
[TR]
[TD]22:50[/TD]
[/TR]
[TR]
[TD]01:45[/TD]
[/TR]
</tbody>[/TABLE]

What i want to do is to categorize those time into 3 categories, which is
CASE 1 = for range time 06:00 to 14:00
CASE 2 = for range time 14:00 to 22:00
CASE 3 = for range time 22:00 to 06:00

I've tried working with below function:
=IF(AND(A2>06:00,G2<14:00),"CASE 1",IF(AND(G2>14:00,G2<22:00),"CASE 2","CASE 3")) but that doesn't work.

please help