Workload Capacity

I am using Excel to plot resources and their workloads. Each resource has various projects that have different start and end dates. Each project requires a certain amount of hours from that resource. Based on a 40 hour work week I have calculated the average percent per day that the resource will spend on that project. I would like to be able to see on any given day each resource's workload percentage capacity. Seen below this resource is at 99% capactiy from 15/12/08 to 20/12/08. From 05/01/09 to 31/01/09 they are at 100% capacity. From 01/02/09 to 04/02/09 they are at 124% capacity. From 05/02/09 to 15/03/09 they are at 24% capacity.

I have a second worksheet that uses the above information to give me a calendar view of what each resource is working on, but I can not figure out how to show workload capacity.

See Attachment.

Please help Not sure if Excel can do what I am looking for.

Hi,

It's not clear, to me at least, how your description above relates to the workbook you attached. For instance you mention 4 periods above but only have two rows of data neither of which covers the period 5/2/09 to 15/3/09.

Can you offer some clarification?

Rgds

In the Alex_Tangi worksheet the datad is as follows:

Docket Start Date End Date Est Hours Act Hours Days %
ITAC9000 15/12/08 20/12/08 20 5 50
ICHV8068 05/01/09 04/02/09 240 30 100
IPON8028 15/12/08 20/12/08 19.5 5 49
IGMC9009 01/02/09 15/03/09 80 42 24

From 15/12/08 to 20/12/08 the resource is at 99% capacity becasue ITAC9000 and IPON8028 are both happening over thse ame time frame.

From 05/01/09 to 31/01/09 they are at 100% capacity as ICHV8068 is the only project during that time frame.

From 01/02/09 to 04/02/09 they are at 124% capacity becasue both ICHV8068 and IGMC9009 are over that time frame.

From 05/02/09 to 15/03/09 they are at 24% capacity for IGMC9009. THis capacity starts on 05/02/09 even though the date is not plotted in the chart becasue it is the first day that ICHV8068 is not overlapping with ICHV8068.

...make sense?

Is there a way to put more then one bit of data into one cell? On the calendar view each cell is a date, and using conditional formatting I am able to highlight the cell if the date matches the resource project data I entered. If I can some how also put in that same calendar cell the percentage capacity used for that day for that project then I could then vertically add the sums of all the percentages in that one day.

Hi,

Not much more I'm afraid. Your original upload only contained the first two of the records you mention above which didn't help, and I've no idea at all what your reference to the 'Alex Tangi' worksheet means. You also refer to a chart which isn't evident anywhere.

Can I suggest you post an example of your original data in its entirety, and mock up a picture or chart of how you want to see the data presented, either in a chart or table. with a few comments. Maybe then we can work out how you get from your data to your final requirement.

Rgds

Maybe the file didn't upload correctly. Please see the atatched gifs...I am trying to upload the Excel as a zip file.

On the calendar worksheet I have highlighted in red what I am trying to calculate automatically based on the data from the Alex_Tangi worksheet.

Do you have an email address I can send the zip file to? It is 1,768KB.