If formula with time

[HockeyDiablo]

Not too good working with time in excel. But what I would like to do is write a formula in an adjacent cell:

If the time is;

06:00-10:00 = AM1
10:00-12:00 = AM2
12:00-14:00 = MID1
14:00-16:00 = MID 2
16:00-18:00 = PM1
18:00-22:00 = PM2

 

[63falcondude]

You can try something along the lines of

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Time[/TD]
[TD="align: center"]Value[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Time[/TD]
[TD="align: center"]Value[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]0:01[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7:00[/TD]
[TD="align: center"]AM1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]6:00[/TD]
[TD="align: center"]AM1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]10:00[/TD]
[TD="align: center"]AM2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]12:00[/TD]
[TD="align: center"]MID1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]14:00[/TD]
[TD="align: center"]MID2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]16:00[/TD]
[TD="align: center"]PM1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]18:00[/TD]
[TD="align: center"]PM2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]22:00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

E2 =LOOKUP(D2,$A$2:$A$9,$B$2:$B$9)

 

[HockeyDiablo]

You can try something along the lines of

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Time[/TD]
[TD="align: center"]Value[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Time[/TD]
[TD="align: center"]Value[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]0:01[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7:00[/TD]
[TD="align: center"]AM1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]6:00[/TD]
[TD="align: center"]AM1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]10:00[/TD]
[TD="align: center"]AM2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]12:00[/TD]
[TD="align: center"]MID1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]14:00[/TD]
[TD="align: center"]MID2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]16:00[/TD]
[TD="align: center"]PM1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]18:00[/TD]
[TD="align: center"]PM2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]22:00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

E2 =LOOKUP(D2,$A$2:$A$9,$B$2:$B$9)

way easier than: =IF((AND(K2>=0.25,K2<=1.416667)),"AM1",IF((AND(K2>4.16667,K2<=0.5)),"AM2",IF((AND(K2>0.5,K2<=0.58333)),"MID1",IF((AND(K2>0.58333,K2<=0.666667)),"MID2",IF((AND(K2>0.666667,K2<=0.75)),"PM1",IF((AND(K2>0.75,K2<=0.916667)),"PM2","AH"))))))

ty

 

[63falcondude]

Sure is! Glad to help.