ORDER TIME TO IDENTIFY SHIFT

[alm395]

Hi!

I have a list of orders with timestamps. I am trying to identify the shift working while that order was placed. I have tried everything I can think of to search the order time to the shift start and end time and cannot figure it out. Please help!

The shifts are:

SHIFT TIME	SHIFT NAME
05:45:00 - 18:15:00	DAY
18:00:00 - 06:00:00	NIGHT
07:00:00 - 15:00:00	RELIEF

Here are some of the formulas I have tried. Some of them are about 95% accurate, but not 100%. I don't know what I am doing wrong.

1. =IF(OR(E2="","",IF(AND(E2>=$A$2,E2<=$B$2),$C$2),IF(AND(E3>=$A$3,E3<=$B$3),$C$3),IF(AND(E4>=$A$4,E4<=$B$4),$C$4),IF(AND(E5>=$A$5,E5<=$B$5),$C$5),IF(AND(E6>=$A$6,E6<=$B$6),$C$6),IF(AND(E7>=$A$7,E7<=$B$7),$C$7)))
2. =IF(E2="","",IF(AND(E2>=A2,E2<=B2),C2,"-"))
3. =IF(OR(AND(E2>=MIN(A2),E2<=MAX(B2)),C2,"-"),(AND(E3>=MIN(A3),E3<=MAX(B3)),C3,"-"),IF(AND(E4>=MIN(A4),E4<=MAX(B4)),C4,"-"),IF(AND(E5>=MIN(A5),E5<=MAX(B5)),C5,"-"),IF(AND(E6>=MIN(A6),E6<=MAX(B6)),C6,"-"),IF(AND(E7>=MIN(A7),E7<=MAX(B7)),C7,"-"),IF(AND(E8>=MIN(A8),E8<=MAX(B8)),C8,"-")))
4. =IFS(AND(E2>=MIN(A2),E2<=MAX(B2)),C2,"-"),(AND(E3>=MIN(A3),E3<=MAX(B3)),C3,"-"),IF(AND(E4>=MIN(A4),E4<=MAX(B4)),C4,"-"),IF(AND(E5>=MIN(A5),E5<=MAX(B5)),C5,"-"),IF(AND(E6>=MIN(A6),E6<=MAX(B6)),C6,"-"),IF(AND(E7>=MIN(A7),E7<=MAX(B7)),C7,"-"),IF(AND(E8>=MIN(A8),E8<=MAX(B8)),C8,"-")))
5. =IFERROR(OR(E7="","",IF(AND(E7>=A7,E7<=B7),C7,"-"),IF(AND(E7>=A8,E7<=B8),C8,"-")),"NIGHT1")
   
   

   Copy of Emergency Orders Report-Monthly2022-07-04-06-00-49.xlsx
   	A	B	C	D	E	F
   1	START TIME 1	END TIME 1	SHIFT		TEST	SHIFT RESULT
   2	05:45:00	05:59:59	AM SHIFT CHANGE		05:50:01
   3	06:00:00	6:59:59	DAY		06:10:00
   4	07:00:00	14:59:59	RELIEF / DAY RUN		12:00:00
   5	15:00:00	17:59:59	DAY		16:10:00
   6	18:00:00	18:14:59	PM SHIFT CHANGE		18:10:00
   7	18:15:00	11:59:59	NIGHT		03:10:00
   8	00:00:00	5:44:59	NIGHT		03:10:00
   Sheet7 (2)

 

[6StringJazzer]

How do you want to treat times that fall into overlaps in shift times?

Last 15 minutes of Day is the same as the first 15 minutes of Night
Last 15 minutes of Night is the same as the first 15 minutes of Day
The entire Relief shift is contained within Day

Night hours cross midnight so that has to be taken into account in the formulas

I also don't understand how the Excel data you are showing us relates to the description of the shift times above. For example, start time for Night doesn't match in the two places.

If a time falls into a shift change interval, is that the result you want to show?

Please show the desired results in your Excel data (currently blank under SHIFT RESULT)

 

[alm395]

How do you want to treat times that fall into overlaps in shift times?

Last 15 minutes of Day is the same as the first 15 minutes of Night
Last 15 minutes of Night is the same as the first 15 minutes of Day
The entire Relief shift is contained within Day

Night hours cross midnight so that has to be taken into account in the formulas

I also don't understand how the Excel data you are showing us relates to the description of the shift times above. For example, start time for Night doesn't match in the two places.

If a time falls into a shift change interval, is that the result you want to show?

Please show the desired results in your Excel data (currently blank under SHIFT RESULT)

Hi!

Sorry for the confusion. Please see the table for the overlapping time option I came up with. The last 2 rows are the full night shift, I just broke it down since it goes past midnight. I wasn't sure what else to do.

Copy of Emergency Orders Report-Monthly2022-07-04-06-00-49.xlsx
	A	B	C	D	E	F	G
1	Notes	START TIME 1	END TIME 1	SHIFT		TEST	SHIFT RESULT
2	overlap	05:45:00	05:59:59	AM SHIFT CHANGE		05:50:01	AM SHIFT CHANGE
3	day shift - overlap	06:00:00	6:59:59	DAY		06:10:00	DAY
4	normal shift	07:00:00	14:59:59	RELIEF / DAY RUN		12:00:00	RELIEF / DAY RUN
5	day shift - overlap	15:00:00	17:59:59	DAY		16:10:00	DAY
6	overlap	18:00:00	18:14:59	PM SHIFT CHANGE		18:10:00	PM SHIFT CHANGE
7	night shift - overlap	18:15:00	11:59:59	NIGHT		03:10:00	NIGHT
8	overnight shift - overlap	00:00:00	5:44:59	NIGHT		03:10:00	NIGHT
Sheet7 (2)

 

[FormR]

Hi, if you can sort your lookup table in ascending order by the start time 1 column, then you can use a simple lookup() formula.

Book1
	A	B	C	D	E	F	G
1	Notes	START TIME 1	END TIME 1	SHIFT		TEST	SHIFT RESULT
2	overnight shift - overlap	00:00:00	05:44:59	NIGHT		05:50:01	AM SHIFT CHANGE
3	overlap	05:45:00	05:59:59	AM SHIFT CHANGE		06:10:00	DAY
4	day shift - overlap	06:00:00	06:59:59	DAY		12:00:00	RELIEF / DAY RUN
5	normal shift	07:00:00	14:59:59	RELIEF / DAY RUN		16:10:00	DAY
6	day shift - overlap	15:00:00	17:59:59	DAY		18:10:00	PM SHIFT CHANGE
7	overlap	18:00:00	18:14:59	PM SHIFT CHANGE		03:10:00	NIGHT
8	night shift - overlap	18:15:00	11:59:59	NIGHT		03:10:00	NIGHT
Sheet1

Cell Formulas
Range	Formula
G2:G8	G2	=LOOKUP(F2,$B$2:$B$8,$D$2:$D$8)

If you wanted to do it without the lookup table - you could try:

=LOOKUP(F2,0+{"00:00","05:45","06:00","07:00","15:00","18:00","18:15"},{"NIGHT","AM SHIFT CHANGE","DAY","RELIEF / DAY RUN","DAY","PM SHIFT CHANGE","NIGHT"})

 

[alm395]

Hi, if you can sort your lookup table in ascending order by the start time 1 column, then you can use a simple lookup() formula.

Book1
	A	B	C	D	E	F	G
1	Notes	START TIME 1	END TIME 1	SHIFT		TEST	SHIFT RESULT
2	overnight shift - overlap	00:00:00	05:44:59	NIGHT		05:50:01	AM SHIFT CHANGE
3	overlap	05:45:00	05:59:59	AM SHIFT CHANGE		06:10:00	DAY
4	day shift - overlap	06:00:00	06:59:59	DAY		12:00:00	RELIEF / DAY RUN
5	normal shift	07:00:00	14:59:59	RELIEF / DAY RUN		16:10:00	DAY
6	day shift - overlap	15:00:00	17:59:59	DAY		18:10:00	PM SHIFT CHANGE
7	overlap	18:00:00	18:14:59	PM SHIFT CHANGE		03:10:00	NIGHT
8	night shift - overlap	18:15:00	11:59:59	NIGHT		03:10:00	NIGHT
Sheet1

Cell Formulas
Range	Formula
G2:G8	G2	=LOOKUP(F2,$B$2:$B$8,$D$2:$D$8)

If you wanted to do it without the lookup table - you could try:

=LOOKUP(F2,0+{"00:00","05:45","06:00","07:00","15:00","18:00","18:15"},{"NIGHT","AM SHIFT CHANGE","DAY","RELIEF / DAY RUN","DAY","PM SHIFT CHANGE","NIGHT"})

That worked! Thanks so much!!!