How should I layout this data to make pivot tables and charts show correcetly?

I have a massive amount of data which I have tried to put into pivot tables and charts and such, but the rows and columns consistently end up in awkward places and can't seem to be fixed. So I am wondering if my initial data could be represented in a better format to make everything else a bit easier. My Data is somewhat like this:

Please Login or Register  to view this content.

I have simplified the whole thing so you get the idea. But in fact there are 50 projects, each with 3 locations and 11 work types. And the dates are weekly and go across two years.

Anyway the data is quite easy to understand as it is currently laid out, but I find that when I want to manipulate the data in charts and tables it is not very cooperative. In particular it doesn't much like having the dates along the top. It also seems to not like how there is 3 columns before my values instead of 1.

My main goal is to create a stacked area chart with dates on the horizontal and total amount of work on the vertical. The legend should contain a selection of projects with their work totals plotted for each week. I will also probably make a pivot table outlining work totals for each date separated by either type or location.

Where would you start with this?

Any help appreciated - and sorry to start off with such a whopping question...

Not sure. That's kind of my question...

These are my options as I see it:

A) Rearrange my data so that Pivot Tables work for me without too much trouble
B) Somehow overcome the numerous Pivot Table issues I am having with my current data layout
C) Use some other method to generate dynamic tables or charts (Advanced formulas? VBA?)
D) Go to another application... Access maybe

Obviously, this thread revolves around option A. But if one of the other options seems better, I am very open to making changes if it is conventional and will lead to a permanent solution

Anyone got any more information? Why or How I should choose A? I really need some feedback on this

Well I have come up up with one partial solution myself. It seems that Pivot Tables prefer there to be only one column of dates. So instead of having all the date columns I changed to just the one FTE column for values one date column where I list which date the entry refers to - then I duplicated all my existing rows for each date entry. In order to enter my data into this it will come out at around 200,000 rows so that's a problem. But I tested with just 8 weeks of data and the pivot tables/charts worked to my liking