Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've got a table of processes and their respective start and end times (calculated from the start time and duration), listed by the process ID:

Run ID  Duration    Start Time  End Time
200878  464     5/16/11 4:15    5/16/11 11:59
200879  76      5/16/11 4:22    5/16/11 5:38
200880  165     5/16/11 6:29    5/16/11 9:14
200881  44      5/16/11 9:44    5/16/11 10:28

I need a way to pivot the data into "timeslots" to count the number of running processes during each hour using Excel's built-in PivotTables.

For the data excerpt above, I want this chart:

Timeslot Start  Timeslot End    Running Processes
5/16/11 3:00    5/16/11 4:00    0
5/16/11 4:00    5/16/11 5:00    2
5/16/11 5:00    5/16/11 6:00    2
5/16/11 6:00    5/16/11 7:00    2
5/16/11 7:00    5/16/11 8:00    2
5/16/11 8:00    5/16/11 9:00    2
5/16/11 9:00    5/16/11 10:00   3
5/16/11 10:00   5/16/11 11:00   2
5/16/11 11:00   5/16/11 12:00   1

My current workaround uses the SUMIFS function to count the rows that fit into each timeslot. This actually reports the exact statistics I want, but with the following drawbacks:

  • The "Timeslot start" times have to be manually entered, and extended to cover the range of times represented in the original table.
  • None of the filtering mechanisms of Excel's native PivotTables work, like slicers, or arranging by other parameters (my actual data has other fields besides "Run ID" that was removed for brevity)

Thus, if possible, I'd like some way to get the same data with "real" PivotTables.

I've also messed around with adding grouping by Hour/Day/Month/Year in a PivotTable, but even with the "Show items with no data" option, the fact that processes can last more than one hour causes under-reporting (processes that span over a certain timeslot but do not start or end in it aren't counted for that timeslot.)

For your experimenting, here's an xlsx file with some data and my own SUMIFS-based pivot: http://dl.dropbox.com/u/123900/timeslot%20pivot.xlsx

share|improve this question
add comment

1 Answer 1

I honestly don't see a way to handle this with a built-in Excel pivot table. However, you can create your own table to display the data you need. The only manual bit you'll need to do is to create the columns of slot start and end times for which you want process counts. Once you have that, an array formula can be used to get the process counts.

=COUNT(IF((H2>=$C$2:$C$50)*(H2<=$D$2:$D$50)+(I2>=$C$2:$C$50)*(I2<=$D$2:$D$50)>0,1,""))

Enter this as an array formula by pressing Ctrl+Shift+Enter. Column H holds Timeslot Start, column I holds Timeslot End, Column C holds Start Time in your process table, and Column D holds End Time in your process table. Note that you can set the formula to look well beyond the bottom of your process table, so if the list of process is constantly changing, your count formula will still return the correct result.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.