Section 6 - Calculation of Job Stop Time Through the Calendar

Manufacturing Corner on

Saturday, November 26, 2022

 This gets complex, so you can skip this part if you wish and just accept that the forulae work when you apply them, or you can

persevere and understand how the formulas work. The start of the job could be within a working period, or it could fall between two

working periods. Here is the calendar again:

Stages of the calculation:
8:30 AM is after period 2 begins						2	=MATCH(E16,B9:B14)
8:30 AM is after period 1 ends						1	=MATCH(E16,C9:C14)
8:30 AM lies between the beginning and end of period 2						2	=G21+(G21=G22)
period 2 begins at 8:00 AM						8	=INDEX(B9:B14,G23,1)
period 2 ends at 10:00 AM						10	=INDEX(C9:C14,G23,1)
you can start the job at 8:30 AM because it is within a working period						8.5	=MAX(G24,E16)
the number of hours from the start of the job to the next break						1.5	=G25-G26
cum hours at end of period 2						2	=INDEX(E9:E14,G23,1)
the job starts at cum hour 0.5 of the calendar						0.5	=G28-G27
the job stops at cum hour 7.5 of the calendar						7.5	=G29+E17
the job stops during period 5						5	=MATCH(G30,E9:E14)+1
period 5 ends 9 working hours into the calendar						9	=INDEX(E9:E14,G31,1)
period 5 ends at hour 18 (6:00 PM)						18	=INDEX(C9:C14,G31,1)
the job will stop 1.5 hours before period 5 ends						1.5	=G32-G30
the job will stop at hour 16.5 (4:30 PM)						16.5	=G33-G34
With a series of substitutions, the 15 formulas can be condensed into 4 as follows:
Calc1	2	=MATCH(E16,B9:B14)+(MATCH(E16,B9:B14)=MATCH(E16,C9:C14))
Calc2	0.5	=INDEX(E9:E14,B39,1)-(INDEX(C9:C14,B39,1)-MAX(INDEX(B9:B14,B39,1),E16))
Calc3	5	=MATCH(B40+E17,E9:E14)+1
Job Stop	16.5	=INDEX(C9:C14,B41,1)-INDEX(E9:E14,B41,1)+B40+E17
The meanings of the formulas are:
Calc1	8:30 AM lies between the beginning and end of period 2
Calc2	the job starts at cum hour 0.5 of the calendar
Calc3	the job stops during period 5
Job Stop	the job will stop at hour 16.5 (4:30 PM)

 

Labels: Scheduling Excel Basic

Thanks for reading Section 6 - Calculation of Job Stop Time Through the Calendar. Please share...!