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

	A	B	C	D	E
1	Time	Value		Time	Value
2	0:01			7:00	AM1
3	6:00	AM1
4	10:00	AM2
5	12:00	MID1
6	14:00	MID2
7	16:00	PM1
8	18:00	PM2
9	22:00

<tbody>
</tbody>

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

 

[HockeyDiablo]

You can try something along the lines of

	A	B	C	D	E
1	Time	Value		Time	Value
2	0:01			7:00	AM1
3	6:00	AM1
4	10:00	AM2
5	12:00	MID1
6	14:00	MID2
7	16:00	PM1
8	18:00	PM2
9	22:00

<tbody>
</tbody>

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.