• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Schedule tasks

pubcrawler

New Member
I wonder if someone can help with some scheduling help.

I have a list of tasks that have different durations.
I have a number of facilities which I would like to distribute the tasks across
When the task is complete (task end date) the next task in the list will go to that facility.

I need to automate this as i have a task list which is changing daily and is 160 tasks long. I need to communicate to a customer expected start date of x task.
i am struggling to generate a new start date and identify a facility for a task in the list where the date clashes with a facility which is already executing a task.

I have tried a number of formula, min(), Vlookup() if(). I did think of using VBA to loop through the list of tasks assign a facility and then check dates, but i keep getting lost trying to copy similar examples and modifying the code. any help would be much appreciated.
Please find layout attached which i hope explains it a bit better
 

Attachments

  • Facility.xlsx
    9.6 KB · Views: 6
The attached contains only the beginnings of a solution (the formulae are very probably much longer than they need be, but I've run out of time).
Formulae added from cell D19, copied down.
Note that the column headed End Date your formula is not actually the end date but the day after (if a task starts on the first day, has a duration of one day it actually ends on the same day).
So far the formulae don't require a version of Excel with the function FILTER available to you, which can't be said for the formulae at cell C3 copied down.
 

Attachments

  • Chandoo48260Facility.xlsx
    18.9 KB · Views: 7
hi p45cal - that is excellent and appreciate the time you have spent on this. This works well for when there is no clash, although if a number of the tasks are submitted with the same start date, that is when i need to calculate a new start date due to the resource constraint.
 
Not sure I'll be able to help but why don't you attach a workbook with such clashes as well as rules as to how dates can/cannot be changed. Perhaps with an example of how the results might look with those clashes.
For example, can dates be pushed forwards as well as back? If so, it looks as though we can ignore the start dates after the first ones have been started then allocate new tasks as soon as any facility has finished its previous task.

Also, what version of Excel are you using and do you have the FILTER function available to you?
 
Last edited:
Hi, work book attached - the dates can only be pushed back. I am using office 365.
 

Attachments

  • Chandoo48260Facility_copy.xlsx
    19.4 KB · Views: 2
In the file you just attached, you have an unusual date:
79649

when corrected it chooses Facility a for that job and your comment on that row doesn't apply because facility a was previously finished 21Feb2015 (which remember isn't the end date but the day after, the day that that facility is available for a new project; remember:
Note that the column headed End Date your formula is not actually the end date but the day after
). So at the moment I see no clashes in the file:
79650


Only if job no. 8 is brought forward to 20Feb2015 does it produce #N/A in the Facility column:
79651

At the moment I can only suggest manually advancing the Start Date for Job no. 8 until the #N/A disappears (I've attached a workbook where I've added a spin button at cell A9; what this does is to add or subtract a day in the selected cell, but only if that cell is a single cell and in the range B12:B100) but I'll work on something to advance the date automatically, but you may not get anything today!

In the meantime, if you could produce a realistic set of jobs with clashes it might help me find a working solution.

Since you're using Office 365, the formulae starting at cell C3, if they're not showing #N/A, show the schedules for each facility where it's easy to see if their jobs overlap.
 

Attachments

  • Chandoo48260Facility_copy01.xlsm
    31.7 KB · Views: 8
Last edited:
thanks very much for your help. The customers drop in jobs when they want them done. I can only do 6 at a time, so i need to provide feedback to the customer when the job can be done, sometimes i can have +10 jobs with all the same date, so if an automatic date update can be done then thats perfect. This is only a small section - i generally get 100+ job requests a day. So I need to provide feedback on when their work is likely to be done.
 
I added a file with a spin button in just at the same time you posted the last message:
I've attached a workbook where I've added a spin button at cell A9; what this does is to add or subtract a day in the selected cell, but only if that cell is a single cell and in the range B12:B100
 
Back
Top