Solved: Scheduling suggestions

[Tommy]

Looking for suggestions/ideas:

Background:

I have jobs that come in and it has been determined (not by me) how long this job will take to do.

Work flow::whip

Part 1 - person does job.
Part 2 - somebody checks it.
Part 3 - person fixes problems found.
Part 4 - somebody makes sure changes have been made.
Part 5 - job is released somewhat on schedule.

What I need ideas on is how to show I have this guy tied up on job 1812 for 36 hours and this job will take 18 hours to check. I currently have 2 employees and 1 checker (me). More to be added when I can find them. I will have a limit of 11.

I would like to have a visual on who is scheduled and the due dates and the job, when I am scheduled, when he is expected to be finished when I am expected to be finished, what's next ... the usual stuff.

What I would ideally like is to add a job, set the hours required for 2 fields and assign the job, after looking to see who will need work in time to make the due dates. The times can and will overlap though I would prefer not to, it will. I can also have more than 1 person working on a job. I will also have incidentals that will need to be done prior to "working" on the job, for example I would need to order material in advance so the items can be ordered and delivered in time for fabrication.

What I was thinking::dunno

Some semblance of a bar chart on a calendar, the bar being the time required and can be broken up, other bars that are "attached" for the incidentals and checking. The bar would be "throbbing" when late/past due.

Everything should be able to be adjusted. I have other information that I would put in an access database, but that would be a one shot deal and is a no brainer.:think:


Currently I have a list, at the most, so anything would be better than what I have. :yes

Hopefully at some future date I will actually start getting jobs before they are due instead of after. LOL :banghead:

I can and will code this, I am just looking for some ideas on what to do. With the vast experience that I have seen here I think someone will give me some ideas on what paths to follow and which ones not to take due to too much for too little and save me some time. Also keep my brain cramps to a minimum, hopefully. OK we know that won't happen but I can hope for the best right?:rofl:

I appreciate any advice.

Thanks for your time and attention in this matter in advance.

Tommy

[Bob Phillips]

Google on Excel Gantt charts, there are dozens around.

[lucas]

Tommy, I don't know if this will help with your problem but it is what I use and Malcolm helped me set it up some time back.

Just put a one (1) in the column that reflects where you are on the job. Change the headers, etc. to reflect your needs.

hope this helps, it works for me but I only have a few jobs running at a time. You could create visuals from the info fairly easily.

I just bought a new HP computer so I hadn't moved it over to the new one yet......took a minute to set up the network which I needed to do anyway.

I bought what I think is a nice one.
64bit
quad 2.4 gh processors
6 gb ram
1 terabyte hd.......actually it's two in a raid 0 array.
22 inch monitor...

I feel like I've caught up with the world except for my internet connection. Guess satellite is next for those of us in rural America. We are lagging on this.

[Tommy]

xld (may I call you Bob?), the Gantt charts are similar to what I want/need, at least I think so, there are a lot of different approaches to this. Thanks.

Steve, that shows me more of the things I need to add to this little project, Thanks. All of this would be for your progress cell.

I really like the Gantt chart, the thing is I would have to figure out how to overlay them, kinda I guess, to get what I am trying to do, maybe.:rotlaugh:Now with Steve's suggestion I thought of several other items I (need to) (would like to) add. A lot of it I don't have to worry about yet, but it's good to know now, because I flat didn't think of them.:giggle

Now I've read
http://www.vbaexpress.com/forum/showthread.php?t=21203
and something similar might work, but do I really want to do that right away. LOL

I have some ideas so far and am beginning to wonder what have I got myself into.:doh:

[Bob Phillips]

Tommy,

What do you mean by overlay them?

I have a nice Gantt, but it still needs a it of work to make it general.

[Tommy]

I'm still working on this so forgive me if I am not very clear. As I find out about things I could and may change my mind.

That being said, what I have in mind is I have the week to schedule, I have 3 jobs, I know how many hours each one is supposed to take. The chart would be something like
1 bar for doing the job
1 bar for checking the job
1 bar for fixing
1 bar for making sure it got fixed

each bar would be number of hours so I would overlay/put bars end to end until the week is filled with a job/jobs. All the while knowing that a job can be anywhere from 2 hours to 112 hours (so it would need to split onto the next week or another employee).

We used to keep a poster board up in front of the room for this. Of course it was manual so you could make it up as you go along. But basically it was 2-3 weeks on a page with each person's name, and job number in each day until it was finished. But that was when it all was done by hand, now you can do the same job in a third of the time, so it gets complicated quick. Things move around ....

I am trying to get the upper hand while it's simple. :rofl:

[brettdj]

Hi Tommy,

The Gantt chart is the way to go - but not in Excel. Try using Microsoft Project if available as part of your Office suite, or one of the shareware programs. They will let you can build in automatic relationships between activities so that if you change say the checking time, then the fixing and final check time positions will also update

Hope you are raising hell with the fish :)

Cheers

Dave

[Bob Phillips]

Those jobs would just be tasks in PM speak, but hours is problemmatical, because 10 hours say would span 2 days. Days are much easier to plot.

[lucas]

Tommy,
I'm not sure where you're headed with this but here is another excel possibility for you to examine.

I used this extensively when working in the field. It helped me keep track of multiple tasks on multiple pieces of equipment that were required for the completion of a job.

Concentrate on the drums sheets. There are two, one is a data(Drums) sheet and the other is a chart of the data on the drums sheet.

On the main drums sheet: click on several of the cells to the right of one of the drums and add a date to show work completed and then review the changes on the chart sheet. Both sheets are useful.

[Tommy]

Well I have a working solution with this. :thumb
I do have 1 glitch that decided to show up but I haven't been able to reproduce it so it could have been me just as easy as the code. :dunno
The usage is pretty simple otherwise it defeats the purpose. HA

When you first open the sheet you will be asked for the number of jobs per day and the number of employees. I went with 4 jobs per day and 9 employess. Yes I have grown a little. :) The form will pop up only on first run. You will also be asked to select a color to represent the employee. Once this inial step is complete the spreadsheet wil automatically generate a templete. There will be a toolbar inserted also. The toolbar alows you to schedule the "job", the information required is the job number (or name or ...) the due date and the number of hours to do the job, the number of hours to check the job. This will enter the data into the sheet. Now to do the scheduling itself. Select the job on the "calander" and the form will pop up asking if this is going to be detailing, checking ... select one and pick the cell that you want the work to start in then pick ok and the job is scheduled for that person. Same for all the rest of the selections.
When the spreadsheet first opens it will check to see if a job is overdue and highlight it in a redish background if the job has not shopped (in other words finished :)). Today will also highlight in the job area so you know at a glance where today is.
You can add notes for the job also.

I gave credit where credit was due in the code. I did forget to mention that the toolbar code was developed on/from/with this site.

This is still a WIP and I may or may not do anything else with it. Right now it does exactly what I need.:yes

[Bob Phillips]

It fails over here, presumably because of the date format.

[Tommy]

Do you get the master sheet? I can add something to check local settings for the date. If you got the master sheet you are correct because I used a / for date seperators.

I also found the glitch.

[Bob Phillips]

I got the sheet, all formatted, and the toolbar. It was added a task (I think that was what I did) from the toolbar that crashed it. I put in a date of 16th Feb or something similar, and it was trying to use a date of 15/28/2011, which I guess it got from the fact that I entered my date as 16/2/2011 in the textbox.

[Tommy]

This should fix the issue Bob, I tested with German settings so I should be getting the correct date seperator and format.

I fixed the glitch, it was when the last week of the previous month and the first week of the month were the same week.
I added a pattern to the scheduled job in the cell to determine if the job was being checked, detailed, scrubbed or backlooked.