Mooboos

New Member
Joined
Mar 9, 2016
Messages
8
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?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

borkarrr

New Member
Joined
May 23, 2015
Messages
44
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")))
 
Last edited:

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
Hi, welcome to the forum.

Another option for you to consider:

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Time</td><td style=";">Shift</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;color: #333333;;">18:44</td><td style="background-color: #FFFF00;;">Evening</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;color: #333333;;">21:49</td><td style=";">Evening</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;color: #333333;;">02:59</td><td style=";">Night</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;color: #333333;;">06:54</td><td style=";">Morning</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=LOOKUP(<font color="Blue">A2,0+{"00:00","06:00","14:00","22:00"},{"Night","Morning","Evening","Night"}</font>)</td></tr></tbody></table></td></tr></table><br />
 

Mooboos

New Member
Joined
Mar 9, 2016
Messages
8
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Time</td><td style=";">Shift</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;color: #333333;;">19:30</td><td style="color: #333333;background-color: #FFFF00;;">Evening</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;color: #333333;;">08:36</td><td style="color: #333333;;">Morning</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;color: #333333;;">14:02</td><td style="color: #333333;;">Evening</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=LOOKUP(<font color="Blue">A2,0+{"00:00","06:00","14:00","22:00"},{"Night","Morning","Evening","Night"}</font>)</td></tr></tbody></table></td></tr></table><br />
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 
Last edited:

Mooboos

New Member
Joined
Mar 9, 2016
Messages
8

ADVERTISEMENT

A1B2
19:30:00Night

08:36:00Night
14:02:00Night
14:04:24Night
In B2 I have ^

<tbody>
Code:
[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>
</tbody>
 

Mooboos

New Member
Joined
Mar 9, 2016
Messages
8
=LOOKUP(A2,0+{"00:00","06:00","14:00","22:00"},{"Night","Morning","Evening","Night"})
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
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"})
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,331
Members
414,446
Latest member
CRAVIN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top