Time format to number while keeping value

[Guest]

Hello,

Thanks for the help. For inbound calls I use software called ‘Discovery
ACD.’ I am able to export a report from the ACD software into Excel. The
problem with the export is that the data shows somewhat like a fractional
number. See “Imported Ave. Call Time†below.

In order to show the actual Ave. call time I take this number (say
323.71791) and divide the number by 86,400 (the number of seconds in a day).
The result of the division is ‘0.0037467.’

I then take this number and format the cell to custom “m:ss.†This format
then produces the 5:24.

What is happening is that when I use a Pivot Table the value is showing as
12:05:24 AM.

Question: How do I change the cell to show the time I need, while also
changing the format of the cell to a number.

Store # Calls Imported Ave. Call Time time should look like
Store 1 50 323.71791 5:24
Store 2 43 280.37209 4:40
Store 3 19 343.10526 5:43
Store 4 1 44 0:44
Store 5 8 330.25 5:30
Store 6 6 513.33333 8:33
Store 7 12 259.08333 4:19
Store 8 17 244.52941 4:05
Store 9 33 305.48485 5:05


Thanks

 

[JE McGimpsey]

You just need to change the PT field format.

XL's stored values don't differentiate between times of day and elapsed
times, so 0.00374673506944444 will display as 5:24 (Format m:ss) or
12:05:24 AM (Format: h:mm:ss AM/PM) or 00:05:24 (Format hh:mm:ss),
depending on the display format.

 

[Guest]

One solution is to deal with minutes only:

323.71791 divide by 60 to get 5.3952985 minutes rather than 5 minutes 24
seconds.


Then run the pivot table.