Excel Task Scheduling!

[u104741]

Im not sure if this is the best place to ask.. but here goes..

Im trying to find a formula or VBA function that finds the most efficient sheduling for a given set of tasks over a period of time within Excel.

Essentially i need to be able to take a given set of tasks all with specific durations, in hours, and periodic frequencies throughout a year (eg monthly, weekly, 3 monthly etc...) and work out the most efficent schedule for those tasks to minimise the number of man hours per week!

I appreciate there are lots of if's and but's in there but if anyone has had success in doing something along those lines i would love to know how you approached it.

cheers,
jj

 

[ETID]

Do you have a snipet example of the data and format that you will be using to collect this data?

 

[u104741]

To be honest I'm not 1005 sure what data I need to capture, although below i have tried to show a sample...

If i could get the principles of how to write some sort of function that can take this data and work out the most efficent Schedule to minimise the weekly man hours yet, complete thwe whole plan in a 52 week period I would be a happy man.

If you cunt ad paste the text below into a spreadsheet it should look more sensible than it does here.

Cheers,
JJ


Asset Code Task Description Total Estimated Time Hrs PPM Frequency Start Week No 31 32 33 34 35 36 37 38
GPS-A001-ALL-01 A001 HOT & COLD WATER SERVICES (GEN) 1.00 1 Month 32 0 1M 0 0 0 1M 0 0
GPS-A001-ALL-01 A001 HOT & COLD WATER SERVICES (GEN) 1.50 1 Yearly 36 0 0 0 0 0 1Y 0 0
GPS-A011-BASE-01 A011 Trace Heating 1.00 3 Monthly 36 0 0 0 0 0 3M 0 0
GPS-A011-BASE-01 A011 Trace Heating 1.00 1 Yearly 36 0 0 0 0 0 1Y 0 0
GPS-A011-ROOF-01 A011 Trace Heating 2.00 3 Monthly 36 0 0 0 0 0 3M 0 0
GPS-A011-ROOF-01 A011 Trace Heating 2.00 1 Yearly 36 0 0 0 0 0 1Y 0 0

 

[retiring]

Try a google search on excel linear regression. I think this is what you are looking for.

Regards

 

[u104741]

will do thanks.

 

[pbrodsky]

I know almost nothing about the subject, but I think what you are looking for is called "linear programming" or "linear algebra".

You might try looking a Excel's "solver" to see whether that can do what you want.