If formula with time

HockeyDiablo

Board Regular
Joined
Apr 1, 2016
Messages
180
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

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
You can try something along the lines of

ABCDE
1TimeValueTimeValue
20:017:00AM1
36:00AM1
410:00AM2
512:00MID1
614:00MID2
716:00PM1
818:00PM2
922:00

<tbody>
</tbody>

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

HockeyDiablo

Board Regular
Joined
Apr 1, 2016
Messages
180
You can try something along the lines of

ABCDE
1TimeValueTimeValue
20:017:00AM1
36:00AM1
410:00AM2
512:00MID1
614:00MID2
716:00PM1
818:00PM2
922: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
 

Forum statistics

Threads
1,085,693
Messages
5,385,224
Members
401,936
Latest member
stephenpoff

Some videos you may like

This Week's Hot Topics

Top