Wednesday, June 14, 2017

yogi_Key-in WorkDay Hours For Projects Given 'Start Date' 'End Date' And 'WorkLoad' Per Day

Google Spreadsheet   Post  #2184
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Jun-14-2017
question by Trevor Brightman:!topic/docs/XYZcDFjNGb0;context-place=forum/docs
Plotting workload across dates
I'm looking for a way to automatically plot workload across a series of dates (calendar) so I can forecast the workload of a given day to assure our staffing levels will meet demand.

Attached is an example with the general idea.

Table 1 simply lists the projects with start and end dates, as well as a =NETWORKDAYS calculation of the number of week days between the start and end.

I also added a "workload" column (total hours for the project) and using the weekdays I calculated how many hours that project would be needed to be worked on each week day.

Here comes the tricky part. I want Table 2 to automatically plot the daily workload onto the calendar dates. I've filled in the data manually (in green) but I'm wondering if it can be done with some sort of formula.

Ideally this would allow me to quickly and easily tally the workload needed for various projects as workload and due dates change in Table 1.

I hope this makes sense, and I hope someone can help me out,

Thanks Internet Friends!