Hi the solution is as follows:
Step1: When we convert a time in to a number, of course 7am (start of first shift) is a lesser number than 7pm (start of second shift) so you will have convert time in to number format. You link your starting time to Column E of the data in Number format.
Step2: Enter Shift Start time (7am and 7pm) on same or another sheet and use MATCH function with TYPE 1 so that if it doesnt find the exact match, it return the closest lower time's position. In such a case, it will return N/A in a few times (between 7pm to 12midnight) as these exceed the times given in table. You edit the formula to return "2" in case of such an error.
Step3: On another sheet, you can manually maintain that the shift starting 7am on any day is 1st and that on 7pm is 2nd. Concatenate it with day (i.e. Mon1, Mon2, Tue1, Tue2 and so on). Give this column any name like Shift Code.
Step4: On your data sheet, you link the date in Column G, custom format "ddd" and concatenate it with sheet nos. (1 or 2) that you have generated in Step2 above. This will have the same values as in the other sheet column named Shift Code. This gives you a unique value to match between both sheets.
Step5: Using VLOOKUP you can get your desired Shift Name (A,B,C,D etc.) from Sheet2.
This may be a long solution but in future you will have to just copy Sheet1 values to your file, copy columns E,F,G,H from Sheet2 and correct formula ranges (3 steps). You can hide unnecessary columns later on. It would be interesting to see if anybody comes up with a simpler solution. I have emailed you back the completed file.
Moazzam
Sheet1
| A | B | C | D | E | F |
1 | Shift Start | | | | | |
2 | 7:00:01 | | | | | |
3 | 19:00:01 | | | | | |
4 | | | | | | |
5 | Day | Time From | Time To | Code | Shift No. | Shift Name |
6 | Mon | 7:00:01 | 19:00:00 | Mon1 | 1 | A |
7 | Mon | 19:00:01 | 7:00:00 | Mon2 | 2 | B |
8 | Tue | 7:00:01 | 19:00:00 | Tue1 | 1 | A |
9 | Tue | 19:00:01 | 7:00:00 | Tue2 | 2 | B |
10 | Fri | 7:00:01 | 19:00:00 | Fri1 | 1 | A |
11 | Fri | 19:00:01 | 7:00:00 | Fri2 | 2 | B |
12 | Sat | 7:00:01 | 19:00:00 | Sat1 | 1 | A |
13 | Sat | 19:00:01 | 7:00:00 | Sat2 | 2 | B |
14 | Sun | 7:00:01 | 19:00:00 | Sun1 | 1 | A |
15 | Sun | 19:00:01 | 7:00:00 | Sun2 | 2 | B |
16 | Wed | 7:00:01 | 19:00:00 | Wed1 | 1 | C |
17 | Wed | 19:00:01 | 7:00:00 | Wed2 | 2 | D |
18 | Thu | 7:00:01 | 19:00:00 | Thu1 | 1 | C |
19 | Thu | 19:00:01 | 7:00:00 | Thu2 | 2 | D |
20 | | | | | | |
21 | Formulae: | | | | | |
22 | D6=A6&E6 and so on. All other entries are manually written | | | | | |
<colgroup>
<col style="width: 30px; font-weight: bold;">
<col style="width: 68px;">
<col style="width: 73px;">
<col style="width: 76px;">
<col style="width: 52px;">
<col style="width: 61px;">
<col style="width: 76px;"></colgroup>
<tbody>
</tbody>
Sheet2
| A | B | C | D | E | F | G | H |
2 | Work center | Prod Reference No. | Start Date | Start time | Time
-> Number | Shift
No. | Shift
Code | Shift
Name |
3 | 143 | SS-100758 | 1/21/2013 | 9:03:00 AM | 0.3770833 | 1 | Mon1 | A |
12 | 143 | W-30133 | 1/6/2013 | 1:27:00 AM | 0.0604167 | 2 | Sun2 | B |
19 | 143 | W-30812 | 1/10/2013 | 3:09:00 AM | 0.1312500 | 2 | Thu2 | D |
86 | 143 | W-32692 | 1/10/2013 | 6:30:00 PM | 0.7708333 | 1 | Thu1 | C |
668 | | | | | | | | |
669 | | Formulas: | | | | | | |
670 | | E3 = D3 and copy it down. Format should
be "Number" | | | | | | |
671 | | F3 = IFERROR(MATCH(E3,Sheet1!$A$2:$A$3,1),2) | | | | | | |
672 | | G3 = TEXT(C3,"ddd")&F3 | | | | | | |
673 | | H3 =
VLOOKUP(G3,Sheet1!$D$5:$F$19,3,FALSE) | | | | | | |
<colgroup>
<col style="width: 30px; font-weight: bold;">
<col style="width: 83px;">
<col style="width: 91px;">
<col style="width: 77px;">
<col style="width: 91px;">
<col style="width: 91px;">
<col style="width: 64px;">
<col style="width: 74px;">
<col style="width: 73px;"></colgroup>
<tbody>
</tbody>