Indicate shift type coresponding to the time of day in a cell...

**Alwpg** · 01-02-2012, 01:52 AM

I have 3 possible shifts: Day, Evening & Night.
Days are 7:45-16:00, Evenings are 15:45-24:00 and Nights are 23:45-8:00.
I want a formula that will tell me what shift an employee works based on the start time.

eg.
D3=7:45 AM, F3=4:00 PM (this is a day shift)
D4=3:45 PM, F4=12:00 AM (this is an evening shift)
D5=11:45 PM, F5=8:00 AM (this is a night shift)

How can I get Cell I3 to display the word "Day" or "Evening" or "Night" properly?

I have attached a sample sheet.

thanks.

**arlu1201** · 01-02-2012, 02:47 AM

Maybe you can attach a sample workbook so we can see in what formats the values are stored in columns D & F.

**DonkeyOte** · 01-02-2012, 04:41 AM

Originally Posted by Alwpg

I have 3 possible shifts...How can I get Cells I1, I2, I3 to display the word "Day" or "Evening" or "Night"?

Assuming your start times are always one of those three values (as implied)

Please Login or Register  to view this content.

**Alwpg** · 01-02-2012, 08:08 AM

as you indicated, that will only work if the start time is exactly 7,15 or 23. I would also like to know the same info if the shift starts a couple hours late, for example.

**DonkeyOte** · 01-02-2012, 08:27 AM

Perhaps:

Please Login or Register  to view this content.

**Alwpg** · 01-03-2012, 01:52 AM

That works great, thanks. Only one thing...I don't have a clue what it is doing. Would you mind briefly explaining what it is doing?

**DonkeyOte** · 01-03-2012, 05:28 AM

In XL Time is a Decimal value (1 = 24 hours, e.g 6:00 = 0.25).

Please Login or Register  to view this content.

converts the time units such that an hour becomes 1 rather than 1/24 - e.g 6:00 [0.25] -> 6.0

Please Login or Register  to view this content.

above subtracts 7:45*24 from the resulting value (7:45 being the start of the first shift), e.g:

7:30 -> 7.5 -> 0

Please Login or Register  to view this content.

it is obvious that given the subtraction where the start time < 7:45 we will end up with a negative value and MOD ensures we end up with appropriate remainder, e.g:

7 -> -0.5 -> 23.5

Given the subtraction of 7:45 from our start time we know the "times" of interest become:

07:45 -> 00:00 -> 0
15:45 -> 08:00 -> 8
23:45 -> 16:00 -> 16

For each of the above we have a distinct return type so we add a result_vector so instead of returning 8 we return "Evening" etc...