Workload spreadsheet - pivot tables

[KateB]

Wonding if anyone can help? I'm stumped!

I'm trying to create a spreadsheet to help with workload. Each job is
assigned to one person and is given a start date and number of days to
complete. I then calculate start date as the max of the date the
project was entered & the last day that the person was available.
(i.e. can't start on a new job until the previous one is finished).

The end date is then calculated as the start date + workday(number of
days). So far so good!

I then want to pivot by person to add up how many hours they have in
each week, to allow balancing of workload. I have a pivot table
grouped by the job start date (grouped by week), which I've made a
pretty graph of (makes it easy to see who is over / under-utilised).
I'm not sure how to deal with projects which are longer than a week,
or cross over a week boundary. At the moment they are all summing on
the pivot table to the week in which they start, but that means that
there may be 80 hours shown in one week and none in the next week. I
think it would be much easier to use, if it "level-ised" across weeks.

I think it would be possible to write a macro, but I'm trying to not
use them if possible (the end users aren't familiar with coding). I
thinkk array formulae may do the trick, but I'm not sure how to
implent. I can upload my spreadsheet somewhere, if someone tells me
where / how!

Thanks,
Kate

 

[Patrick Molloy]

I think your sum needs to be adjusted so that projects taking more than a
week are "trimmed".

If( EndDate > Monday+4 , 40, counthours)

kind of thing?

 

[KateB]

I think that's close - that will fix the problem in week 1, but how do
I get it to insert the carryover hours in week 2?

Thanks for your help! Much appreciated
Kate

 

[Patrick Molloy]

if the data is in week2 then deduct what came earlier
IF(week=2, hours-40, hours)

 

[KateB]

i'm not sure if we're on the same page - are you talking about doing
this with a macro? Or additional columns? I can see how you
calculate the residual number of hours, but I'm not sure how you get
this to insert as another row for the pivot data. I think you need
another row with the date of the next week, so the pivot will properly
summarise the workload.

Thanks again,
Kate