Pivot Table

[Gerry Bennett]

The problem I have is that I have a worksheet with data as time, formatted
as m:ss.00 were 00 is hundredths of a second. I have noticed that the cell
shows the correct time say 1:34.96 but the formula bar shows it as rounded
up 1:35 this is also carried to the pivot table. so my data for printing is
wrong. Has anyone got an idea how I can over come this problem. thanks Gerry

 

[Debra Dalgleish]

It depends what you need to do with the time in the pivot table, but
perhaps you could convert the time to text in the source data table. For
example, with times in column B, use the following formula:

=TEXT(B2,"mm:ss.00")

Then, add that field to the pivot table.

 

[Gerry Bennett]

Thanks Debra I did try what you suggested in the source data but the pivot
table still did not show the correct time it would only display zero. What I
hope to acheve is that my data holds information such as Swimmers name, date
of birth, swimming stroke and personal best time in that stroke. I have
added filters so you can pull up data for one individule and amend or
display. there are approx 600 rows, I then what to be able to print all or
some of the data so that is why I was using a piviot table. I can get the
information to display in the pivot table except that hundreths of a second
do not show and the time is ether rounded up or down, in the pivot table.
Can you suggest any other way of printing or diplaying this.

 

[Debra Dalgleish]

Since you need the time in the Data area, my suggestion to convert it to
text won't help. The following worked for me:

Change the formula to: =B2
Select the column, and choose Format>Cells
On the Number tab, select General, click OK
Add this field to the pivot table
In the Pivot table, format the field with the custom number format of
mm:ss.00

 

[Gerry Bennett]

Debra thanks for the info that has worked. I have spent weeks trying to sort
it Thanks

 

[Debra Dalgleish]

You're welcome! I'm glad it worked for you.

Debra thanks for the info that has worked. I have spent weeks trying to sort
it Thanks