Converting decimals to Time
 

My data for work time is
1.1
1.017
etc...
I need to convert this to
1:06
1:01
etc..
and have this data be used in a Pivot Table that shows the average time of
all of these cells. When I convert using =TEXT(A1/24,"h:mm"), I get #DIV/0 in
the Pivot Table. Thanks

Converting decimals to Time
 

The /24 is right, but don't use the text function; you want the data to
remain numeric in order to calculate an average!
If you just divide by 24, then format the cells as Time, you should get the
right view. The in the Pivot Table, select the field's number format and
again choose Time.

"Gringarlow" wrote:

My data for work time is
1.1
1.017
etc...
I need to convert this to
1:06
1:01
etc..
and have this data be used in a Pivot Table that shows the average time of
all of these cells. When I convert using =TEXT(A1/24,"h:mm"), I get #DIV/0 in
the Pivot Table. Thanks

Converting decimals to Time
 

Hi Gringarlow,

Perhaps you should simply use =A1/24 and format the result as 'time'.

--
Cheers
macropod
[MVP - Microsoft Word]


"Gringarlow" wrote in message ...
My data for work time is
1.1
1.017
etc...
I need to convert this to
1:06
1:01
etc..
and have this data be used in a Pivot Table that shows the average time of
all of these cells. When I convert using =TEXT(A1/24,"h:mm"), I get #DIV/0 in
the Pivot Table. Thanks

Converting decimals to Time
 

Brilliant - Thank You, sometimes its just too simple to see.

"bapeltzer" wrote:

The /24 is right, but don't use the text function; you want the data to
remain numeric in order to calculate an average!
If you just divide by 24, then format the cells as Time, you should get the
right view. The in the Pivot Table, select the field's number format and
again choose Time.

"Gringarlow" wrote:

My data for work time is
1.1
1.017
etc...
I need to convert this to
1:06
1:01
etc..
and have this data be used in a Pivot Table that shows the average time of
all of these cells. When I convert using =TEXT(A1/24,"h:mm"), I get #DIV/0 in
the Pivot Table. Thanks

Converting decimals to Time
 

Gringarlow wrote:
My data for work time is
1.1
1.017
etc...
I need to convert this to
1:06
1:01
etc..
and have this data be used in a Pivot Table that shows the average time of
all of these cells. When I convert using =TEXT(A1/24,"h:mm"), I get #DIV/0 in
the Pivot Table. Thanks

Don't convert the number to text, divide by 24 and display in an appropriate
time format.