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" wrote in message
...
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