# Time ranges in Excel Modelling

#### Mooboos

##### New Member
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
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
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
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

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
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

ADVERTISEMENT

 A1 B2 19:30:00 Night 08:36:00 Night 14:02:00 Night 14:04:24 Night 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
=LOOKUP(A2,0+{"00:00","06:00","14:00","22:00"},{"Night","Morning","Evening","Night"})

#### Mooboos

##### New Member
Both Columns are on Manual

#### FormR

##### MrExcel MVP
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"})

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.

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

### 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