Compare hours worked to hours expected for period

[shawnw]

I have a worksheet ("Effort") which has one row for each task worked. It includes the date, the worker's name, the task name, and the number of hours of effort. Over the course of a week or a month, for example, I can see who worked on what and for how long. Something like this:

	A	B	C	D
1	DATE	NAME	TASK	HOURS
2	1/1/2016	Joe	Paint	2
3	1/1/2016	Mary	Paint	3
4	1/2/2016	Fred	Bake	6
5	1/2/2016	Joe	Prep	7
6	1/2/2016	Mary	Prep	7
7	1/4/2016	Fred	Prime	8

<tbody>
</tbody>

To be clear, I have data for many months and many people. More data than in the sample above.

I want to see all the effort during a time-box and compare it to the number of work hours in that timebox. Basically, I want to know what percentage f the hours are being being worked.

For example, January:

in January 2016 there were 6 weeks (20 work days) totaling 160 work hours:

• Week 1 has 0 workdays
• Week 2 has 5 workdays x 8 hours = 40 hours
• Week 3 has 5 workdays x 8 hours = 40 hours
• Week 4 has 5 workdays x 8 hours = 40 hours
• Week 5 has 5 workdays x 8 hours = 40 hours
• Week 6 has 0 workdays

Each month may have a different headcount of people (and different people) working varying hours of effort.

I have another worksheet that shows the number of hours I expect from each person in a typical work day. For example:

	A	B
1	Name	Hours per Day
2	Joe	8
3	Mary	2
4	Fred	8
5	Bill	8
6	George	4

<tbody>
</tbody>

Let's assume that these five people (above) worked in January.

Joe = 20 x 8 = 160 hours expected in Jan
Mary = 20 x 2 = 40 hours expected in Jan
Fred =20 x 8 = 160 hours expected in Jan
Bill = 20 x 8 = 160 hours expected in Jan
George = 20 x 4 = 80 hours expected in Jan

Since these are the only people who logged hours in Jan, the total hours expected in Jan is 160+40+160+160+80=600

Now I want to compare the expected hours to the actual hours, by person individually and for the month as a whole. For example:

Joe = 100 hours actual in Jan (vs. 160 expected)
Mary = 40 hours actual in Jan (vs. 40 expected)
Fred = 180 hours actual in Jan (vs. 160 expected)
Bill = 80 hours actual in Jan (vs. 160 expected)
George = 80 hours actual in Jan (vs. 80 hours expected)

Again, the number of work hours expected of each person in Feb or Mar or Apr will be different, because there are a different number of work days in each month and each person has his or her own contract terms. Also, the mix of people who work in any particular month will be different as people roll onto and off of the various tasks.

I will complicate it further by indicating that some typical workdays and holidays and should not be counted in the expected" hours total for the week or the month they're in.

Okay... so now...

It was easy to create a chart that shows the actual effort by work week or month. What I am struggling with is how to show the actual versus the expected for whatever time-box the graph is displaying. For example, I may filter the pivot chart by month or week - or by person - and I need the "expected" hours to adjust dynamically.

How can I do this?

Thanks!