Rotating shift formula

Thanks:  0
Likes:  0

1. ## Rotating shift formula

Does anyone know a formula, using date and time, to calculate a specific work shift?

2-2-3 schedule

A - mon, tues, frid, sat, sun (0700-1900)
B - mon, tues, frid, sat, sun (1900-0700)
C - wed, thur (0700-1900)
D - wed, thur (1900-0700)

Thanks!!

2. ## Re: Rotating shift formula

Hi the result can be achieved in a number of ways. Please explain your requirement. You can email your sample data at ca.moazzam@gmail.com I'll send the worked example.

Moazzam

3. ## Re: Rotating shift formula

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

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)

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•