Grouping by time in pivot table

[ezflyer]

I have a table of results from a triathlon which includes a column for total time in hh:mm:ss. I am trying to create a pivot table that groups the results by time in 30-minute intervals, for example, 8:00:00 - 8:30:00 ... 17:00:00 - 17:30:00. I have tried a number of ways but Excel always wants to display the time as a time of day, not elapsed time. Can anyone suggest a way to do this.

Thanks

Bill

 

[lrobbo314]

You can use the formula below to make a new column with rounded times. Then use that new column to make your pivot table.

Book1
	A	B
1	Times	Rounded
2	3:00:11	3:00:00
3	2:45:09	3:00:00
4	2:23:13	2:30:00
5	2:56:30	3:00:00
6	3:13:24	3:00:00
7	3:45:16	4:00:00
Sheet3

Cell Formulas
Range	Formula
B2:B7	B2	=ROUND(A2*((60/30)*24),0)/((60/30)*24)

 

[ezflyer]

Thanks for the suggestion. The formatting isn't what I was hoping but it gives me the data and I can fix the formatting. I tried this in both Windows and Mac and results are quite different. The table on the left is from Windows, on the right Mac. I do wish Microsoft would create a version of Office that worked the same in both environments. Bill

 

[lrobbo314]

I got the same result in windows that you got on Mac. It's because it's automatically adding a group element to the rows of the pivot table. If you remove the 'Hours' grouping, then it should look right. You can just go to the 'Group' section of the 'Analyze' tab on the ribbon and click 'Ungroup'.

 

[ezflyer]

Unfortunately not. When I remove the Hours, I get the table below. I tried the same actions in Windows and got the result that you described. This is typical of the Mac version of Excel which is reliably almost compatible with the Windows version.

 

[lrobbo314]

Hmm, weird. Another in a long list of reasons I don't use anything Apple.