Results 1 to 3 of 3

Rotating shift formula

This is a discussion on Rotating shift formula within the Excel Questions forums, part of the Question Forums category; Does anyone know a formula, using date and time, to calculate a specific work shift? 2-2-3 schedule A - mon, ...

  1. #1
    New Member
    Join Date
    Jan 2013
    Posts
    1

    Default 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. #2
    Board Regular ca.moazzam's Avatar
    Join Date
    Jan 2011
    Location
    Calgary, Canada
    Posts
    180

    Default 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. #3
    Board Regular ca.moazzam's Avatar
    Join Date
    Jan 2011
    Location
    Calgary, Canada
    Posts
    180

    Default 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)

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com