Help needed taking my Excel project prioritization tool to the next level. Novice needing advice.

mohmann

New Member
Joined
Nov 21, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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:
  • 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)
  • 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 again for looking, I am rather desperate here, any thoughts you may have will certainly be appreciated!!

Thanks,
MO
 

Attachments

  • testing 11-21-21 screenshot.PNG
    testing 11-21-21 screenshot.PNG
    113.1 KB · Views: 20
  • mock-up 11-21-21 screenshot.PNG
    mock-up 11-21-21 screenshot.PNG
    102.8 KB · Views: 17

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,389
Messages
5,769,798
Members
425,572
Latest member
ja189704

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top