alm395
New Member
- Joined
- Apr 23, 2018
- Messages
- 39
- Office Version
- 365
- Platform
- Windows
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:
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.
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.
- =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)))
- =IF(E2="","",IF(AND(E2>=A2,E2<=B2),C2,"-"))
- =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,"-")))
- =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,"-")))
- =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)