Time format to number while keeping value

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

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.




In article <[email protected]>,
Jim <[email protected]> wrote:

> 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

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.

--
Gary''s Student


"Jim" wrote:

> 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
>