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?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

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,726
Office Version
  1. 365
Platform
  1. Windows
Hi, welcome to the forum.

Another option for you to consider:


Excel 2013
AB
1TimeShift
218:44Evening
321:49Evening
402:59Night
506:54Morning
Sheet1
Cell Formulas
RangeFormula
B2=LOOKUP(A2,0+{"00:00","06:00","14:00","22:00"},{"Night","Morning","Evening","Night"})
 

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,726
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?


Excel 2013
AB
1TimeShift
219:30Evening
308:36Morning
414:02Evening
Sheet1
Cell Formulas
RangeFormula
B2=LOOKUP(A2,0+{"00:00","06:00","14:00","22:00"},{"Night","Morning","Evening","Night"})
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,477
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,726
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"})
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,995
Messages
5,856,705
Members
431,828
Latest member
kARTIK12345

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