Time ranges in Excel Modelling

[Mooboos]

Hi There

I have a three shifts
06:00-14:00- Morning
14:00-22:00- Evening
22:00-06:00- Nights

I have a range of times E2 Column such as
18:44
21:49
02:59
06:54
(ect)

I want to return in the column next to the time if the time falls in-between the shift times whether its a Morning, Evening or night shift?

Can anyone help?

 

[borkarrr]

Hello--

It can be done like this--

However you will first need to insert an extra column--
assuming you have eg 18:44 in A1 then in cell b2---

write =N(A1)

(to convert time to numericals--a format that excel will understand in order to use if(or) statement)


and then-- in cell c1 copy paste this formula

=IF(OR(B1<0.25,B1>0.916667),"night",IF(AND(B1>0.25,B1<0.58333),"morn",IF(AND(B1>0.58333,B1<0.91667),"eve","Outofrange")))

 

[FormR]

Hi, welcome to the forum.

Another option for you to consider:

Excel 2013
	A	B
1	Time	Shift
2	18:44	Evening
3	21:49	Evening
4	02:59	Night
5	06:54	Morning
Sheet1

Cell Formulas
Range	Formula
B2	=LOOKUP(A2,0+{"00:00","06:00","14:00","22:00"},{"Night","Morning","Evening","Night"})

 

[Mooboos]

Hi
Both or your formula's paste fine- Thank you

However when I drag the formula down to the next time cell (row 2) it just coppies the same shift name as above
EG

19:30 Night
08:36 Night
14:02 Night

Thanks

 

[FormR]

Hi, the formula copies down fine for me, do you have calculation set to manual? Can you post the exact formula you tried?

Excel 2013
	A	B
1	Time	Shift
2	19:30	Evening
3	08:36	Morning
4	14:02	Evening
Sheet1

Cell Formulas
Range	Formula
B2	=LOOKUP(A2,0+{"00:00","06:00","14:00","22:00"},{"Night","Morning","Evening","Night"})

 

[MARK858]

Is your calculation set to manual as both formulas posted copy down fine for me?

Edit: slow at typing as FormeR replied as I was posting.

 

[Mooboos]

[TABLE="width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]B2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19:30:00[/TD]
[TD]Night

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08:36:00[/TD]
[TD]Night[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14:02:00[/TD]
[TD]Night[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14:04:24[/TD]
[TD]Night[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]

[PHP][PHP][PHP][PHP]=LOOKUP(A2,0+{"00:00","06:00","14:00","22:00"},{"Night","Morning","Evening","Night"})

[/PHP][/PHP][/PHP][/PHP][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
</tr>[TR]
[TD]In B2 I have ^[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Mooboos]

=LOOKUP(A2,0+{"00:00","06:00","14:00","22:00"},{"Night","Morning","Evening","Night"})

 

[Mooboos]

Both Columns are on Manual

 

[FormR]

Do you also have dates in the those cells, if so try:

=LOOKUP(MOD(A2,1),0+{"00:00","06:00","14:00","22:00"},{"Night","Morning","Evening","Night"})