Can I project number of hours per month based off a start and stop date for multiple projects?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

I have a table which tracks information for a number of projects, each project is it's own record. Two of the dates I track are a start and finish date. I also have a number of hours quoted to work on the project in total.

My goal is to build a chart that shows each month with active projects with a sum total of hours quoted per month for all projects. It's unlikely that hours will actually be divided up exactly evenly by month, but as far as resource planing goes it should be close enough.

For example, if I have one project that is scheduled to go from May-August and take 100 hours, each month would be allocated 25 hours. If I had second project from July-August and take 50 hours, each month would be allocated 25 hours again. The ending result would be a bar graph showing May (25 hours) June (25 hours) July (50 hours) and August (50 hours). 

Any ideas on how to complete this, if it's even possible?

Photo of Andrew

Andrew

  • 90 Points 75 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
I have this working for another client and I can do a demo for you.  It's not so easy to do, but I did manage to do it without script (ie all native QuickBase).  The issue is that to make a chart, the chart needs entries for each time period, say each week or each month depending on what you want.  My client wanted resource planning by week to schedule Technician Labor required vs labor available.  The labor available was hand entered into a table, in their case they have 6 technicians, so the one of the lines on the line chart was the available labor - basically 6 man weeks of labor per week.  Then we added in vacation or PTO and that decremented the available labor by week.  ie if 1 of the techs was expected to be off next week, then that week would show a dip to just 5 man weeks of available labor per week.  That was the easy part.

The fancier part was to create the weekly entries of the labor required per Job, for each week that the Job was active.  

The result was actually a beautiful chart which highlighted Labor needs vs Labor available, but week.  They were then able to visualize problems ahead of time and immediately rescheduled some jobs to not create situations which which were either going to be impossible to meet, or would have caused a ton of overtime.  I suppose, that as business picks up they can also visualize the need to hire another tech if no amount of job rescheduling will solve the labor needed.

So it can be done, but its not something that can be explained on this forum as the setup, while native, was complicated.  Contact me via the information in my profile if you would like a demo.
Photo of Katy

Katy

  • 92 Points 75 badge 2x thumb

Hi! I have a similar need (Tasks with start and end dates with a flat utilization % within this duration), and need to be able to show weekly totals. Can you show me your demo, please?

Thanks!

Katy

Hi Katy,
You can contact me via the information on my website  QuickBaseCoach.com  Use my gmail address or the Contact me form.