Pivot Chart not working with minute/second/millisecond format

[ra3ra3]

I am trying to chart how long it took for people to locate a book in a bookstore, using a stopwatch. So, the format is minutes/seconds/milliseconds

I have changed the field to be "Sum" instead of count. And I have tried setting the times to "30:55.2", "37:30:55", "mm:ss.0", and "mm:ss.00"

Nothing is working... any suggestions...?

[MrShorty]

I'm not certain that I understand. It appears that you have entered your times as text strings (min.sec.millisecond in rows 7, 10, and 13). I expect that your operating system is not set up to recognize this kind of time format, so Excel has no idea how to convert these text strings to numbers that it can add, so it ignores the text when performing the summation. I have some ideas for data entry below, but the basic solution to the problem is to enter your time values as real, meaningful numbers and not as meaningless text strings.

Possible data entry options:
1) Because most people's operating systems are set up to recognize h:m:s.ms type of time values, Excel will almost always work well if you will enter your times like that. So C7 would be entered as 0:0:4.24. Excel will then recognize this as a time and convert it to its underlying date/time serial number system (number will be stored as a fraction of a day). The main downside that I think causes most people not to like this is that ":" is an inconvenient character on most keyboards, and the need to enter the leading "0:" hours part feels additionally inconvenient. As a manual data entry method, this isn't as convenient as most would like, but it is how Excel wants to seem times entered.

2) Using a hand calculator or similar, convert your times from sexagesimal number into a decimal number. For this, you might enter something like 0+4.24/60 to get 0.7066666.... (meaning 0.7067 minutes) and enter 0.70666666666667 into C7. Or maybe you would prefer decimal seconds 0*60+4.24=4.24 (meaning seconds). Most people would find it tedious working with the external calculator, and would not like the decimal minute or second value that results. But this would also be a solid number that Excel can easily add up.

3) (and probably the approach I would take), enter my times as mm.ssms (no decimal point between second and millisecond) in a dedicated data entry column (that will only be used for data entry). A helper column can then use the DOLLARDE() function to convert to decimal minutes =DOLLARDE(0.424,60) or =DOLLARDE(C7,60). The pivot table would then use the helper column to perform the summation, so the result in the pivot table is still a decimal minute result.

As I said, the problem is that you are entering text strings that Excel cannot interpret as numbers. Give Excel real numbers to work with, and the pivot table should have no trouble with the summations.