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]

Ok, I think I may have it figured out, but one more question. How many potential overlaps for a single machine on the same day/time could there be?

 

[Dan Reedy]

Wow, that this a good question. I am not sure I can answer that with a high degree of certainty. I would like to think 5 or less.

 

[dreid1011]

Ok, I think that it won't change what I do with 3 or more. I had something that was working for a single overlap (2 parts at the same time), but as soon as I added a 3rd part, that's when it gets a little tricky, but I'll figure it out. I'm nearing the end of my shift for today, but I'll be able to play with it more tomorrow.

 

[Dan Reedy]

Thank you very much for your help!

Ok, I think that it won't change what I do with 3 or more. I had something that was working for a single overlap (2 parts at the same time), but as soon as I added a 3rd part, that's when it gets a little tricky, but I'll figure it out. I'm nearing the end of my shift for today, but I'll be able to play with it more tomorrow.

 

[dreid1011]

Ok, I've been thinking about this, and it might need to be done in VBA, or with multiple helper columns. The reason is that for each additional overlapping part, the adjusted start time needs to build off the previous part's adjusted start time, not the original start time. The formula that I was working with cannot achieve this with a single column I don't think because then it would have to refer to the same range I put it in. So, if VBA is an option, I will have some more questions for you.

 

[Dan Reedy]

VBA is an option, however, I am very limited in my knowledge with it.

 

[dreid1011]

Ok, so, hopefully I don't miss anything important. Questions:

You said the data begins at Row 9 correct? Approximately how many rows would you need to process each time the sheet is updated?

Are new parts added at the top or bottom of the list?

Should I account for the operation sequence, and if so, when?

Should I account for the transfer hours, and if so, when?

Should I account for the setup time, and if so, when?

Should I account for the hours available per day, and if so, when?

Should I account for the start of working hours and end of working hours, and if so, when?

 

[Dan Reedy]

Answers below:

Ok, so, hopefully I don't miss anything important. Questions:

You said the data begins at Row 9 correct? Approximately how many rows would you need to process each time the sheet is updated? Data begins in row 10. Row 9 is the column headers. I am not sure I understand what you are asking as far as how many rows will I need to process each time the sheet is updated. Our current schedule is about 600 lines, I have made this one for 1000.

Are new parts added at the top or bottom of the list? Typically new ones would be added at the bottom, but existing ones may be changed for changes in quantity and or ship date. This may or may not make a difference in your thought process.

Should I account for the operation sequence, and if so, when? I do not believe you will need to account for this.

Should I account for the transfer hours, and if so, when? Yes - transfer hours will affect the start date / time. Not sure what you mean by when.

Should I account for the setup time, and if so, when? No, setup time is accounted for in the remaining hours column.

Should I account for the hours available per day, and if so, when? Yes - not sure what you mean by when. The total hours scheduled in a 24 hour period for this machine should not exceed the number of hours available per day. It should also not be scheduled any hours outside the time available as dictated by the start and end of working hours discussed below.

Should I account for the start of working hours and end of working hours, and if so, when? Yes - Not sure what you mean by when. This only dictates what time of day the cell / machine is available to run.

Hope this helps. If I am not understanding what you are asking for, please let me know and I will try again.

 

[dreid1011]

As far as how many rows, I just wanted to know about how large the data set is. I'm trying to establish how often you might need to adjust the start times for a schedule and if the code should check the whole schedule each time you add a part or make a change or just part of the schedule.

Transfer hours - Are transfer hours figured into remaining hours? If not: If part 1 has remaining hours of 20 and transfer hours of 10, would part 2 be scheduled 20 hours later or 10 hours or perhaps even 30 hours?

 

[Dan Reedy]

Got it. I would suggest that it check the entire schedule

Transfer hours - the scenario you described, part 2 would be scheduled 10 hours later.