Formula to schedule working hours

[sc3runner]

I need to create a scheduling tool that schedules a series of tasks through their sequence of tasks and timings, but within the confines of an 8 hour working day.

Task 1 180 minutes
Task 2 180 minutes
Task 3 240 minutes
Task 4 180 minutes

Task 1 needs to start @ 0800 and finish @ 1100.
Task 2 will start @ 1100 and finish at 1400
Task 3 will start @ 1400, but will only be 50% complete by the end of the working day - i.e. 1600.
This will therefore need to also schedule into Day 2 from 0800 to 1000.
Task 4 will start @ 1000 Day 2, and finish @ 1300.

Is there a formula of method to schedule these tasks within an 8 hour working day, and then move to the next working day for the next tasks?

[peter.abing]

Hi,

Please find attached. That is my idea on how you can do the scheduler but the formula is not really efficient. Maybe someone can also give a solution that I can also copy. I also face a similar situation so I made this long formula.

My solution is like this:
1. Multiply number of completed days by 8 and add the number of hours (currently on schedule) to get the total number of hours completed.
2. The result in 1 is divided by 13 which is the total number of hours per cycle.
3. The result in 2 is subtracted by the result in 2 rounded down to nearest whole number to get the fraction of the current cycle that is being schedule for that hour (example 4 hrs from the start of the cycle is 0.3077 [or 4/13] of the cycle).
4. The result in 3 is multiplied by 13 to get the current hour of the cycle being scheduled (in our example is 4).
5. From result in 4, we chose the equivalent Task as below:
1 = Task 1
2 = Task 1
3 = Task 1
4 = Task 2
5 = Task 2
6 = Task 2
7 = Task 3
8 = Task 3
9 = Task 3
10 = Task 3
11 = Task 4
12 = Task 4
13 = Task 4

Other additions:
A. I rounded the result in 4 to nearest whole number.
B. If result in 4 is 0, it is changed to 13.

Any suggestions how the formula can be trimmed down? Any function that will make this easier? Because I'd also use that formula.

Thanks,
Peter

Task Scheduler.xlsx

[sc3runner]

Peter

Many thanks - great work. As you say, it'd be great if the formula was more efficient - a change to the leadtimes would require quite a bit of rework.

Does anyone else have any insights into excel based production scheduling tools that can schedule sequences of tasks, and workcentres?