Rotating shift formula

bluedtke

New Member
Joined
Jan 28, 2013
Messages
1
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!!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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


ABCDEF
1Shift Start
27:00:01
319:00:01
4
5DayTime FromTime ToCodeShift No.Shift Name
6Mon7:00:0119:00:00Mon11A
7Mon19:00:017:00:00Mon22B
8Tue7:00:0119:00:00Tue11A
9Tue19:00:017:00:00Tue22B
10Fri7:00:0119:00:00Fri11A
11Fri19:00:017:00:00Fri22B
12Sat7:00:0119:00:00Sat11A
13Sat19:00:017:00:00Sat22B
14Sun7:00:0119:00:00Sun11A
15Sun19:00:017:00:00Sun22B
16Wed7:00:0119:00:00Wed11C
17Wed19:00:017:00:00Wed22D
18Thu7:00:0119:00:00Thu11C
19Thu19:00:017:00:00Thu22D
20
21Formulae:
22D6=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


ABCDEFGH
2Work centerProd Reference No.Start DateStart timeTime
-> Number
Shift
No.
Shift
Code
Shift
Name
3143SS-1007581/21/20139:03:00 AM0.37708331Mon1A
12143W-301331/6/20131:27:00 AM0.06041672Sun2B
19143W-308121/10/20133:09:00 AM0.13125002Thu2D
86143W-326921/10/20136:30:00 PM0.77083331Thu1C
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>
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top