Formula to schedule working hours

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?

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

View attachment Task Scheduler.xlsx

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?