it

How to Fix Excel Pivot Table Time Rounding

this table has times that our race

results for three different teams I'm

going to build a pivot table from this

data and we'll see a problem that occurs

when you put time into a pivot table

so first to build the pivot table I'll

select any cell here go into the insert

tab pivot table it has selected my table

we can see in the background I would

like this on an existing sheet so we'll

click and click a starting cell and okay

for the pivot table I want the ID field

in so that's the team ID letter and I

want the time so I'm going to put that

into the values it's showing up as a

count of the time and I would like it as

the max it's all right click summarize

values by max

so showing the maximum time I don't want

it in this general format I'm going to

put it into a time format or right-click

value field settings and in here I'll go

to a number format time and I would like

the tenths of a second to show so I'll

select this time and click OK

I'll click OK again and there are the

times but instead of showing this time

as point two or point three whichever

way it's going to round it it's just

showing a zero here it's not showing us

the numbers that are the tenths or the

hundredths of a second

to fix this problem in a pivot table

there is a workaround that you can use

on going back to the source data and I'm

going to put in another column here so

I'll click where I want the heading I'll

call this time calc I press ENTER it

automatically expands the table to

include that column and in this cell I'm

just going to link to the original time

formula so equals and click on cell c2

that's the time field or you could type

equals c2 if you'd rather have a cell

reference press ENTER and it fills down

now this is general format and that's

the way we're going to leave this over

here we can see that there's a custom

time format then that's what's causing

the problem in our pivot table now I'm

going to refresh the pivot table so that

it understands we've got a new field

right click and refresh and here's our

time calc field so I'll put that into

the values area and it shows up in that

general format

now I'm going to use the same format

that we tried to use here right click

value field settings go into number

format and in here I'll go to time I'll

pick the same format we tried to use

before click OK

okay so let's change that to max and now

we've got 5 minutes 15 seconds and now

it's showing correctly instead of the 0

we're getting the tenths of a second

here so once you've got the new field

working correctly you could take out the

old field for more excel tips and

tutorials and to download the sample

file for this video please visit my

contexture z-- website at

www.decksdirect-dot-com