Calculating Avergae Weekly Workload Based on 2 Columns of Dates

**atmosphereee** · 07-14-2010, 01:33 PM

Okay so I hope I can explain this in a concise way

I am putting together a database and am having trouble calculating the workload by week. I created a gantt chart(stacked column) that illustrated when a specific task was started and when it was completed. Now I created the graph so that the grid lines depict weeks and years, and like I said the chart illustrates the data perfectly, however in order to calculate how many tasks/projects were going on in a given week, I have to manually count how many bars cross my week gridlines. Tedious and almost not worth it. Now I thought I was on the right track by creating a column with just a rundown of weekly placeholders for the past three years, and I was going to try and group them in a pivot table and try to get a raw count of how many tasks were still in process during any of the given weeks, however here is where my dilemma resides... I only have start date, completion date and Duration( Completion - Start) There really is no absolute form of time on excel, just math formulas? My question is how can I trick excel into comparing the (start date + duration + completion date) with the weekly placeholders I set up, in order to calculate the average, min, max, std dev and so forth of my weekly workload?

Sorry for any wordiness.

-Dan

**ChemistB** · 07-14-2010, 01:48 PM

Hard to say without an example spreadsheet (showing dummy data of what you're interested in). You might want to try COUNTIFS or may use SUMPRODUCT with your beginning and ending dates. I don't see why you'd need duration if you have those two pieces of information.

See attached for an example with COUNTIFS. If any of the projects are active for any part of that week, it counts them.

Is this what you are looking for?

**atmosphereee** · 07-14-2010, 02:14 PM

Yes that is pretty much exactly what I need. I also want to be able to use the numbers in a pivot table. How do I autofill so that it it covers every week since 2004? Also is manually putting the date ranges by week the easiest way to do it? I wanna be able to group them in a pivot table so I can compare average weekloads by months and years. What would be the worthwhile way to incorporate =countif into my database so I can use the data computed by countif to make charts that are easily manipulated.