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

Time format to number while keeping value
 

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

Time format to number while keeping value
 

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