How do I "expand" time ranges in Excel?

September 18, 2010 3:03 PM

How do I "expand" time ranges in Excel?

I want to display the hourly activity of a certain system, and my data is in the following form:

Start End Volume
8am 10am 3 units
6am 11am 5 units
... and so on

I'd like to get the following result (ideally using a pivot table):

Hour Volume
6am 5 units
7am 5 units
8am 8 units
9am 8 units
10am 8 units
11am 5 units

How do I tell excel to fill in the missing data?

Thanks in advance.

posted by mpls2 to Computers & Internet (4 answers total)

I suggest the sumifs function. If your data is in A, B, C, strip the am/pm from columns A and B, then put the line of times you're measuring (6, 7, 8, 9, 10, 11) into column E. Then next each of those, in column F, try: =sumifs(C:C, A:A, ">="&e1, B:B, "<="&e1)
posted by ish__ at 3:10 PM on September 18, 2010

Also, if A and B are already in a datetime format, you would need to pull the hour from the datetime out using HOUR() or DATEPART() [i have no idea if either of these functions actually exist, but something like it should...]
posted by ish__ at 3:12 PM on September 18, 2010

I'm confushed as to your data source (probably thinking too hard about it). Does that mean that between 8am and 10am (2 hours) you made 3 parts? So you want to average 1.5parts / hour, ie. 8am 1.5 9am 1.5, and then pivot up the results?
Feel free to send example spreadsheet (email in my profile)
posted by defcom1 at 2:22 AM on September 19, 2010

errr... sorry about the spelling, and memail me for my email address.
posted by defcom1 at 2:24 AM on September 19, 2010