Results 1 to 3 of 3

Thread: Formula to schedule working hours

  1. #1

    Formula to schedule working hours



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    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

  3. #3
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •