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

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.

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

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)

I1
=IF(SUM(D1)=0,"",IF(HOUR(D1)=7,"Day",IF(HOUR(D1)=15,"Evening","Night")))
copied to I3

Perhaps:

=LOOKUP(MOD(D3*24-7.75,24),{0,8,16},{"Morning","Evening","Night"})

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

D3*24

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

D3*24-7.75

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

MOD(D3*24-7.75,24)

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