Pivot Table - Volume by Day and Half Hour

[Ian Herndon]

Hi there! I've asked this similar question different ways through different forums for years now and most often don't even see a reply Hope someone here can help!

Quick summary, I am trying to quantify the amount of volume for each day of the week broken down by half hour.

I have a long list of data which is broken down by hh:mm with a field to the right indicating which day of the week its from. I'm pretty unfamiliar with pivot tables though. How can I take data similar to below and chart out volume on particular days by half hour? 00:00-00:30 would be midnight to 12:30a.

Time of Day Day of Week
00:00 Monday
00:02 Monday
00:02 Monday
00:03 Monday
00:03 Monday
00:03 Monday
00:00 Tuesday
00:01 Tuesday
00:01 Tuesday
00:02 Tuesday
00:04 Tuesday
00:04 Tuesday
00:05 Tuesday


Thanks for any ideas you can suggest!

[Admin]

Hi Ian,

Welcome to ExcelFox !!

Could you please attach a sample workbook ?

[Ian Herndon]

Hi Ian,

Welcome to ExcelFox !!

Could you please attach a sample workbook ?

Thanks! Sure, putting one together now!

[Ian Herndon]

Here ya go. Took a few times of trimming to get it down to the minimum attachment size. =)

[Excel Fox]

You haven't indicated what volume is. What do you mean when you say 'volume'?

[Ian Herndon]

Ah my mistake. Volume you could say is a call, which is indicated by a time. So essentially the times would illustrate call volume during specific times, broken down by day.

[Excel Fox]

Wasn't sure what you were looking for exactly. Hope this is close.

[Ian Herndon]

That's just what I'm looking for! From that I could make a simple chart where the 30min intervals are on the x-axis and the count for each one is a point on the line. Then I'd have a separate line for each day of the week.

Since I'm so new to pivot tables I apologize if this is a basic question. So if I'm reading this correct, the pivot table is actually not reading the E column at all, but using the new time of day labels you made from column H which are then counted, is that right? If so, that seems like a pretty simple way to count the intervals. Great thinking. Trying to build off what you made I went and paste the rest of the data that wouldn't fit in my sample but I noticed the chart doesn't change so I suspect I have to expand the range somewhere. I noticed the formula for the H cells is =VLOOKUP(TIMEVALUE(E4&":00"),$Q$3:$R$50,2) where the only changing text in that is the E# field. Is it possible to drag that down so it duplicates to new rows of data I add for the rest of the days of the week? I tried but figure I might be doing something wrong since none of the numbers changed.

[Excel Fox]

When you say duplicates to new rows of data, do you mean the look up range of cells? Or the data in your original table? Maybe you want to explain with a sample. By the way, if you want to post a larger file, try using something like box.net or other file sharing sites, and post the link here.

[Ian Herndon]

Actually I got it figured out! I made a second pivot table right below it and just kind of copied the placement of each of the field names into the various sections, row labels, values, etc. That is sooo much faster than what I was doing before it's crazy. Also, I noticed what was required to do that was pretty basic, yet really powerful at the same time. You've been a great help, ty so much sir!