Scheduling

[Dan Reedy]

I am in the process of creating a scheduling spreadsheet for a small manufacture. We are scheduling machines, not people. I am just about complete, having everything but the following working well:

When scheduling 2 jobs that use the same machine and need to be done at the same time, it will double book the machine, instead of completing 1 task, then scheduling the 2nd. Where do I begin to make this work as desired?

Thank you

Dan

 

[dreid1011]

Welcome to the board.

Can you provide a small sample of a couple machines being double booked and how you would like the results to be? And explain any job priorities if any.

 

[Dan Reedy]

I don't have the spreadsheet here at home, it is at work, but perhaps I can explain it enough. Part 1 goes through machines 1,3,5. Part 2 goes through machines 2,3,4. Currently, each part is scheduled independently based on the operation start and end dates. These are calculated based on resource work hours (I have a separate spreadsheet that tracks no working days and hours - that is all working fine) Unfortunately, Part 1 and Part 2 are scheduled in isolation, however depending on due dates, the parts can be fighting for the same machine at the same time. Currently the operation dates (and ultimately the job ship date) is the only determining factor for the order in which they are being produced, but it doesn't prevent the overlap. I could easily add a priority column - 1,2,3,4 etc. if needed.

 

[dreid1011]

I think see. A priority column could help. Are updates made to the sheet as parts are finished with each particular machine or is it a one time deal where the whole schedule is laid out up front and nothing changes from beginning to end?

 

[Dan Reedy]

The schedule is updated daily as parts are finished.

I think see. A priority column could help. Are updates made to the sheet as parts are finished with each particular machine or is it a one time deal where the whole schedule is laid out up front and nothing changes from beginning to end?

 

[dreid1011]

A priority column may just be the easiest to work with, especially if two different parts have the same start date and end up needing the same machine at the same time. Otherwise, I would like to see the sheet or a sample of it to better understand what you really have.

 

[Dan Reedy]

I have included a small sample of what it is I am trying to accomplish. The 2 highlighted rows share the same operation start date and time so the cell (1002 in this case) is double scheduled. In this case, sequence 1 of each job should be schedule in subsequent order instead of at the same time. How do I do this? I can easily add a priority column to dictate what should be run first, but then I would need help with the formula or vba to execute the premise of what I am trying to accomplish.

[TABLE="width: 849"]
<tbody>[TR]
[TD="class: xl74, width: 85, bgcolor: #FFE699"]Sequence
[/TD]
[TD="class: xl75, width: 96, bgcolor: #FFE699"]Cell / Machine
[/TD]
[TD="class: xl74, width: 79, bgcolor: #FFE699"]Setup Time
[/TD]
[TD="class: xl75, width: 70, bgcolor: #FFE699"]Hourly Rate
[/TD]
[TD="class: xl75, width: 70, bgcolor: #FFE699"]Hours Available Per Day
[/TD]
[TD="class: xl75, width: 60, bgcolor: #FFE699"]Transfer Hours
[/TD]
[TD="class: xl75, width: 80, bgcolor: #FFE699"]Remaining Hours
[/TD]
[TD="class: xl76, width: 113, bgcolor: #FCE4D6"]Start of Working Hours
[/TD]
[TD="class: xl76, width: 113, bgcolor: #FCE4D6"]End of WorkingHours
[/TD]
[TD="class: xl76, width: 127, bgcolor: #FCE4D6"]Operation Start Date and Time
[/TD]
[TD="class: xl76, width: 124, bgcolor: #FCE4D6"]Operation End Date and Time
[/TD]
[TD="class: xl76, width: 113, bgcolor: #FCE4D6"]Days Operation is Early or Late (+ is late / - is early)
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #D9E1F2"]1
[/TD]
[TD="class: xl66, bgcolor: #D9E1F2"]1002
[/TD]
[TD="class: xl66, bgcolor: #D9E1F2"]2
[/TD]
[TD="class: xl66, bgcolor: #D9E1F2"]600
[/TD]
[TD="class: xl66, bgcolor: #D9E1F2"]15.5
[/TD]
[TD="class: xl66, bgcolor: #D9E1F2"]0
[/TD]
[TD="class: xl67, bgcolor: #D9E1F2"]30.8
[/TD]
[TD="class: xl68, bgcolor: #D9E1F2"]12:00 AM
[/TD]
[TD="class: xl68, bgcolor: #D9E1F2"]11:59 PM
[/TD]
[TD="class: xl69, bgcolor: #D9E1F2"]4/16/18 7:00 AM
[/TD]
[TD="class: xl69, bgcolor: #D9E1F2"]4/17/18 10:18 PM
[/TD]
[TD="class: xl67, bgcolor: #D9E1F2"]-12.4
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]2
[/TD]
[TD="class: xl72, bgcolor: transparent"]M11
[/TD]
[TD="class: xl72, bgcolor: transparent"]0.5
[/TD]
[TD="class: xl72, bgcolor: transparent"]150
[/TD]
[TD="class: xl72, bgcolor: transparent"]21.6
[/TD]
[TD="class: xl72, bgcolor: transparent"]8
[/TD]
[TD="class: xl65, bgcolor: transparent"]131.7
[/TD]
[TD="class: xl70, bgcolor: transparent"]12:00 AM
[/TD]
[TD="class: xl70, bgcolor: transparent"]11:59 PM
[/TD]
[TD="class: xl64, bgcolor: transparent"]4/16/18 3:53 PM
[/TD]
[TD="class: xl64, bgcolor: transparent"]4/24/18 5:59 PM
[/TD]
[TD="class: xl65, bgcolor: transparent"]-5.5
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]3
[/TD]
[TD="class: xl72, bgcolor: transparent"]5003 - Furnace
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[TD="class: xl72, bgcolor: transparent"]600
[/TD]
[TD="class: xl72, bgcolor: transparent"]22.5
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[TD="class: xl65, bgcolor: transparent"]28.8
[/TD]
[TD="class: xl70, bgcolor: transparent"]12:00 AM
[/TD]
[TD="class: xl70, bgcolor: transparent"]11:59 PM
[/TD]
[TD="class: xl64, bgcolor: transparent"]4/25/18 5:59 PM
[/TD]
[TD="class: xl64, bgcolor: transparent"]4/27/18 12:18 AM
[/TD]
[TD="class: xl65, bgcolor: transparent"]-3.3
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]4
[/TD]
[TD="class: xl72, bgcolor: transparent"]6069
[/TD]
[TD="class: xl72, bgcolor: transparent"]0.5
[/TD]
[TD="class: xl72, bgcolor: transparent"]150
[/TD]
[TD="class: xl72, bgcolor: transparent"]18
[/TD]
[TD="class: xl72, bgcolor: transparent"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]123.7
[/TD]
[TD="class: xl70, bgcolor: transparent"]12:00 AM
[/TD]
[TD="class: xl70, bgcolor: transparent"]11:59 PM
[/TD]
[TD="class: xl64, bgcolor: transparent"]4/25/18 11:19 PM
[/TD]
[TD="class: xl64, bgcolor: transparent"]5/4/18 3:02 PM
[/TD]
[TD="class: xl65"]4.3
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #D9E1F2"]1
[/TD]
[TD="class: xl66, bgcolor: #D9E1F2"]1002
[/TD]
[TD="class: xl66, bgcolor: #D9E1F2"]2
[/TD]
[TD="class: xl66, bgcolor: #D9E1F2"]600
[/TD]
[TD="class: xl71, bgcolor: #D9E1F2"]15.5
[/TD]
[TD="class: xl66, bgcolor: #D9E1F2"]0
[/TD]
[TD="class: xl71, bgcolor: #D9E1F2"]23.0
[/TD]
[TD="class: xl68, bgcolor: #D9E1F2"]11:00 PM
[/TD]
[TD="class: xl68, bgcolor: #D9E1F2"]3:30 PM
[/TD]
[TD="class: xl69, bgcolor: #D9E1F2"]4/16/18 7:00 AM
[/TD]
[TD="class: xl69, bgcolor: #D9E1F2"]4/17/18 2:30 PM
[/TD]
[TD="class: xl67, bgcolor: #D9E1F2"]-12.7
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]2
[/TD]
[TD="class: xl72, bgcolor: transparent"]M12
[/TD]
[TD="class: xl72, bgcolor: transparent"]0.5
[/TD]
[TD="class: xl72, bgcolor: transparent"]150
[/TD]
[TD="class: xl73, bgcolor: transparent"]18.0
[/TD]
[TD="class: xl72, bgcolor: transparent"]4
[/TD]
[TD="class: xl73, bgcolor: transparent"]84.5
[/TD]
[TD="class: xl70, bgcolor: transparent"]12:00 AM
[/TD]
[TD="class: xl70, bgcolor: transparent"]11:59 PM
[/TD]
[TD="class: xl64, bgcolor: transparent"]4/16/18 12:20 PM
[/TD]
[TD="class: xl64, bgcolor: transparent"]4/23/18 12:51 AM
[/TD]
[TD="class: xl65, bgcolor: transparent"]-7.3
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]3
[/TD]
[TD="class: xl72, bgcolor: transparent"]5003 - Furnace
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[TD="class: xl72, bgcolor: transparent"]600
[/TD]
[TD="class: xl73, bgcolor: transparent"]22.5
[/TD]
[TD="class: xl72, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]21.0
[/TD]
[TD="class: xl70, bgcolor: transparent"]12:00 AM
[/TD]
[TD="class: xl70, bgcolor: transparent"]11:59 PM
[/TD]
[TD="class: xl64, bgcolor: transparent"]4/24/18 12:51 AM
[/TD]
[TD="class: xl64, bgcolor: transparent"]4/24/18 9:51 PM
[/TD]
[TD="class: xl65, bgcolor: transparent"]-5.4
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]4
[/TD]
[TD="class: xl72, bgcolor: transparent"]6072
[/TD]
[TD="class: xl72, bgcolor: transparent"]0.5
[/TD]
[TD="class: xl72, bgcolor: transparent"]150
[/TD]
[TD="class: xl73, bgcolor: transparent"]18.0
[/TD]
[TD="class: xl72, bgcolor: transparent"]4
[/TD]
[TD="class: xl73, bgcolor: transparent"]84.5
[/TD]
[TD="class: xl70, bgcolor: transparent"]12:00 AM
[/TD]
[TD="class: xl70, bgcolor: transparent"]11:59 PM
[/TD]
[TD="class: xl64, bgcolor: transparent"]4/24/18 6:11 AM
[/TD]
[TD="class: xl64, bgcolor: transparent"]4/30/18 6:41 PM
[/TD]
[TD="class: xl65"]0.5
[/TD]
[/TR]
</tbody>[/TABLE]


Thank you

A priority column may just be the easiest to work with, especially if two different parts have the same start date and end up needing the same machine at the same time. Otherwise, I would like to see the sheet or a sample of it to better understand what you really have.

 

[dreid1011]

I'm sure we can figure out a formula to get you going. Do you already have one that you are using now to establish the schedule or is it all manual entry?

 

[Dan Reedy]

The operation start date and time formula for the first operation is: =if(J9="Y",K9,D9) J9 is simply Y or N to determine if I want to override the standard start date calculation. The standard start date is in D9 and is the ship date minus standard lead time. The override date is in K9.

For subsequent operations it is: =IF(Q23>0,U22+(Q23/P23),V22+1)

The Operation End Date is:

=WORKDAY(U22,INT(R22/P22)+IF(TIME(HOUR(U22),MINUTE(U22),SECOND(U22))+TIME(MOD(R22,P22),MOD(MOD(R22,P22),1)*60,0)>T22,1,0),Calendar!$G$5:$G$55)+IF(TIME(HOUR(U22),MINUTE(U22),SECOND(U22))+TIME(MOD(R22,P22),MOD(MOD(R22,P22),1)*60,0)>T22,S22+TIME(HOUR(U22),MINUTE(U22),SECOND(U22))+TIME(MOD(R22,P22),MOD(MOD(R22,P22),1)*60,0)-T22,TIME(HOUR(U22),MINUTE(U22),SECOND(U22))+TIME(MOD(R22,P22),MOD(MOD(R22,P22),1)*60,0))

This is meant to take into account start of working hours, a calendar with non working days and week days. This needs to be returned in date and time format for the days operation is early or late to work. There is probably easier ways to do that, but it is what I found when I "googled it"

Thank you

I'm sure we can figure out a formula to get you going. Do you already have one that you are using now to establish the schedule or is it all manual entry?

 

[dreid1011]

Can you tell me what is in each of the cells in those first two formulas, and how they relate to the sample you provided? And where will the priority column be and how should it work?