Data format in pivot table

P

Peterso

I am running a Pivot table on some swim data. Even though the data is
formatted the same way "mm:ss.00", the fraction of the second is not
showing up or is not part of the numbers in the Pivot table.

Pivot table data

Back 25 Breast 25 Fly 25
00:31.00
00:27.00 00:28.00
00:31.00 00:33.00
00:31.00 00:36.00
00:31.00

00:27.00 00:28.00
00:23.00 00:25.00 00:24.00


Data the Pivot table is based on

7 CMSA-SE 00:21.87 00:21.49
6 BMAC-SE 00:22.95 00:21.91
7 BMAC-SE 00:23.13 00:22.16
6 BMAC-SE 00:27.97 00:22.63
8 BMAC-SE 00:21.07 00:22.70
7 UN-SE 00:00.00 00:22.94
6 CMSA-SE 00:26.36 00:22.97
8 BMAC-SE 00:00.00 00:23.11
8 GPAC-SE 00:00.00 00:23.14
8 BMAC-SE 00:22.86 00:23.74
8 CMSA-SE 00:22.55 00:24.52
7 CMSA-SE 00:27.15 00:26.66
8 BMAC-SE 00:25.50 00:27.30
6 CMSA-SE 00:00.00 00:28.42
6 UN-SE 00:27.18 00:28.87
6 BMAC-SE 00:30.59 00:30.27
7 BMAC-SE 00:28.24 00:31.22


Any ideas why the fractional seconds are lost?

Peter
 
D

Dave Peterson

I don't know why the fractional part of the seconds are lost, but you could work
around it.

Either format that column of times as general, then run your pivottable and
format those pivottable columns as mm.ss.00. But if you change the raw data
columns back to time formatting, the next time you update the pivottable, you'll
lose those fractions.

I think I'd add another couple of columns and just use simple formulas:
=C2
and
=d2

And format as general and drag down.

Then use these helper columns as the basis for you pivottable.

And format the pivottable results as: mm.ss.00

It seemed to behave nice for me--even after refreshing the pivottable.

(I used xl2003 to test.)
 
P

Peterso

Dave:

Thank you, that worked. It seemed that formatting it twice the
fractional seconds get lost.

Peter
 
Top