|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 23rd, 2009, 04:47 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 
|
|
#2
April 23rd, 2009, 05:00 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#3
April 23rd, 2009, 05:02 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 news  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
|
|
#4
April 23rd, 2009, 05:07 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#5
April 23rd, 2009, 06:00 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
