Tracking time by Activity over a week period using Pivots

I think you did the best you can do given the way the data is organized. To get a more flexible result, you'll have to normalize the data as shown in the table on the Normalized Data Sheet. A pivot table can do a lot with data formatted this way.

The table off to the side is for the purposes of data validation. Since I am using Excel tables, the validation carries down automatically each time a row is added. Since the lookup for the validation is also in a table, when you add an activity to the orange table, it's available on the dropdown.

It's not as bad as it looks. This is nothing more than your original schedule except the days are strung down vertically instead of side-by-side.

As it stands, the pivot table counts the number of 5 minute periods devoted to the activities each day. If you want actual time, I suggest a helper column with the formula =5/1440 - there are 1440 minutes in a day so 5/1440 is a five minute time period. I show that as the second pivot table on the Pivot Table Sheet.

Thanks dflak,

Your solution is essentially perfect for what I want to do and I'm assuming using excel tables will also give me many more ways to interpret my data should I choose to - so thank you!
I do have just a couple more questions, firstly I'd prefer to have the days listed side by side (so I can visually compare what I was doing at each time each day), I know you mentioned this as an issue and I also had a play around and it doesn't look like that's possible without making the pivot table go crazy with levels (is that what they're called?). So I figure the best option is to just make 7 separate pivot tables linked to each individual data table per day, but my question is is there a way to 'sum' pivot tables? or a way to extract just the totals from each pivot and sum them in a new table without the manual copy paste? All I need in regard to the totals is simply 2 columns, one with the activity name and the other with how frequently they occurred (this is just to make a pie chart for how the week's time was spent).

Essentially, I'd love to have it be that the only thing I need to do is plug in the activity at the time I do it and have everything else happen automatically, from the pivots to the graph (this might be dreaming but I know excel is a powerful tool so fingers crossed). I do know I can do this per day, as making pie charts linked to pivots is easy and automatically updates, it's just a question of whether or not I can get weekly totals by activity without having to enter all the data in one table.

EDIT: I've figured out how to do the below, I've just added an extra column into the table called 'category' and then tweak the pivot table as needed
Second question is pretty far fetched but thought I'd ask just in case it was possible. Is there a way to make excel recognise and count 'categories' rather than the whole text? For example, say I wanted to use 'Food' as a category with 'cooking' 'eating' etc as sub categories, is there a way to enter this into the table so that excel just picks up the 'food' component and groups all the activities containing food together in the pivot? eg listing 'Food: cooking', 'Food: eating' would make all activity entries with either those names appear in the pivot table under just one category of 'Food'. Hope that makes sense! I know it's a long shot that it exists since excel is more of a powerhouse with numerical data and this is as categorical as you can get!

Thanks again for your solution & taking the time to help me out, I really appreciate it! It's definitely what I'm after and will be useful even without the above mentioned tweaks.

There's a way to do what you want in the original request, but it will take a bit of VB code. We could put a change event on the cells you want to fill in, and then kick off a program to organize that data into the table I suggested and then refresh the pivot table. This will happen every time something is changed on the weekly calendar, but there is so little data, that it would appear to be instantaneous.

I don't understand the second request. Could you provide a mock-up of what the data entry would look like and what the desired result would look like? It sounds like something that could be implemented. You'll probably need another table that associates categories and sub-categories.