Hello there,
I am attempting to create a project planning and management tool for our workplace. What this entails is obtaining "work hours", an "in date", and an "out date" for each project. The program will divide the number of working hours over each business day that a project may be worked on. When multiple projects are active, (in-between in and out dates), we will receive a sum of total average working hours needed to stay on pace with each project. If we have a daily limit of man hours that are available, we can see where some days we may be over-allocated, or other days we are light on work and can fit in some other projects.
My prototype for this concept works well for all of the above, but is not meaningful if we cannot take it a step further. This is where my novice skill falls short. Here's what I'd like the program to do: Prioritize each project based on the soonest-approaching deadline (out-date), then by higher size of project (work hours). This will happen by the project appearing higher on the rows. Then based on the number of available hours for each particular day, the program will fill today with hours up to the limit, and then allocate the rest to the following day(s). Once the highest priority project has been allocated, the next highest priority project will have it's workload allocated to the next days. The goal is to estimate whether we will be able to take on proposed projects based on our current workload, or amend the proposed project's deadline so that we can accept the job.
I hope that this is possible, and am certain that there is some elegant way to handle these operations. I would greatly appreciate any guidance on what may be the best way to handle this. Is the structure that I have shown in the attachment sensible? What optimizations could I make to suit this better? Should this require VBA? ARRAY? VLOOKUP? Tables? Once again I have very limited experience with excel but very optimistic that you all may be able to help me out here. Even if there is already a program out there that does exactly this, or if I should be looking into Access, Python, whatever you think may be a better solution.
---
Here is a more detailed explanation of the attached screenshot:
Thanks,
MO
I am attempting to create a project planning and management tool for our workplace. What this entails is obtaining "work hours", an "in date", and an "out date" for each project. The program will divide the number of working hours over each business day that a project may be worked on. When multiple projects are active, (in-between in and out dates), we will receive a sum of total average working hours needed to stay on pace with each project. If we have a daily limit of man hours that are available, we can see where some days we may be over-allocated, or other days we are light on work and can fit in some other projects.
My prototype for this concept works well for all of the above, but is not meaningful if we cannot take it a step further. This is where my novice skill falls short. Here's what I'd like the program to do: Prioritize each project based on the soonest-approaching deadline (out-date), then by higher size of project (work hours). This will happen by the project appearing higher on the rows. Then based on the number of available hours for each particular day, the program will fill today with hours up to the limit, and then allocate the rest to the following day(s). Once the highest priority project has been allocated, the next highest priority project will have it's workload allocated to the next days. The goal is to estimate whether we will be able to take on proposed projects based on our current workload, or amend the proposed project's deadline so that we can accept the job.
I hope that this is possible, and am certain that there is some elegant way to handle these operations. I would greatly appreciate any guidance on what may be the best way to handle this. Is the structure that I have shown in the attachment sensible? What optimizations could I make to suit this better? Should this require VBA? ARRAY? VLOOKUP? Tables? Once again I have very limited experience with excel but very optimistic that you all may be able to help me out here. Even if there is already a program out there that does exactly this, or if I should be looking into Access, Python, whatever you think may be a better solution.
---
Here is a more detailed explanation of the attached screenshot:
- There is a column for each of some number of relevant dates, where all but the two weeks prior and after today's date are faded out.
- Each cell displaying a daily project work hour value contains a formula like this
- =IF(AND(WEEKDAY(LR$1,2)<=5,LR$1>=$A2,LR$1<=$B2),$C2/NETWORKDAYS($A2,$B2),"")
- (this formula probably made your blood run cold)
- =IF(AND(WEEKDAY(LR$1,2)<=5,LR$1>=$A2,LR$1<=$B2),$C2/NETWORKDAYS($A2,$B2),"")
- In it's current state, in the case of the column for the date of 11/22, the estimated daily workload has not exceeded the daily workload limit.
- We'd like to have the program divide the number of work hours for project13 to 8, 8, and 6 for 11/22 - 11/24.
- Then for project15, the program will fill in 2 hours on 11/24, hitting the daily workload limit for that day, and then 4 hours each the two following workdays, and so on.
- Ideally, this operation should be carried out for the entire spreadsheet, and be snappy enough to play around with project in and out dates, as well as work hours, holidays, irregular workload limit schedules, etc.
- I have also attached a screenshot of what ideally should be occurring for further explanation.
Thanks,
MO
