| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to convert time expressed as a decimal to actual hours or minu
I am receiving a report from a vendor that lists call time minutes of usage
in decimal form, i.e. 5.15. When I try to convert this figure into actual time format using the formula, =TEXT(G36/24,"H:MM:SS"), my return is 5:09:00. It appears this formula is returning the amount of time after midnite and not what I am looking for, which is the time expressed in actual hours and or minutes used. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to convert time expressed as a decimal to actual hours or minu
Hi
Is 5.15 equivalent to 5 minutes and 15 seconds? If so, you could use the following to convert to Excel time (remember to format the formula cell in a time format): =(A1+MOD(A1,1)*40/60)/1440 1440 being the number of minutes in a day. Richard "ascottbag-hcm" wrote in message ... I am receiving a report from a vendor that lists call time minutes of usage in decimal form, i.e. 5.15. When I try to convert this figure into actual time format using the formula, =TEXT(G36/24,"H:MM:SS"), my return is 5:09:00. It appears this formula is returning the amount of time after midnite and not what I am looking for, which is the time expressed in actual hours and or minutes used. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to convert time expressed as a decimal to actual hours or minu
Why not just
=A1/1440 -- Regards, Peo Sjoblom "Richard Schollar" wrote in message ... Hi Is 5.15 equivalent to 5 minutes and 15 seconds? If so, you could use the following to convert to Excel time (remember to format the formula cell in a time format): =(A1+MOD(A1,1)*40/60)/1440 1440 being the number of minutes in a day. Richard "ascottbag-hcm" wrote in message ... I am receiving a report from a vendor that lists call time minutes of usage in decimal form, i.e. 5.15. When I try to convert this figure into actual time format using the formula, =TEXT(G36/24,"H:MM:SS"), my return is 5:09:00. It appears this formula is returning the amount of time after midnite and not what I am looking for, which is the time expressed in actual hours and or minutes used. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to convert time expressed as a decimal to actual hours or minu
Peo
As my reply asked, it depends on what is represented by the .15. If it means 15/100 of a minute then your formula is indeed the correct one. Richard "Peo Sjoblom" wrote in message ... Why not just =A1/1440 -- Regards, Peo Sjoblom "Richard Schollar" wrote in message ... Hi Is 5.15 equivalent to 5 minutes and 15 seconds? If so, you could use the following to convert to Excel time (remember to format the formula cell in a time format): =(A1+MOD(A1,1)*40/60)/1440 1440 being the number of minutes in a day. Richard "ascottbag-hcm" wrote in message ... I am receiving a report from a vendor that lists call time minutes of usage in decimal form, i.e. 5.15. When I try to convert this figure into actual time format using the formula, =TEXT(G36/24,"H:MM:SS"), my return is 5:09:00. It appears this formula is returning the amount of time after midnite and not what I am looking for, which is the time expressed in actual hours and or minutes used. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to convert time expressed as a decimal to actual hours or minu
Yes you are correct, sometimes I read questions too fast. I just went with
the what the OP tried with his TEXT formula. -- Regards, Peo Sjoblom "Richard Schollar" wrote in message ... Peo As my reply asked, it depends on what is represented by the .15. If it means 15/100 of a minute then your formula is indeed the correct one. Richard "Peo Sjoblom" wrote in message ... Why not just =A1/1440 -- Regards, Peo Sjoblom "Richard Schollar" wrote in message ... Hi Is 5.15 equivalent to 5 minutes and 15 seconds? If so, you could use the following to convert to Excel time (remember to format the formula cell in a time format): =(A1+MOD(A1,1)*40/60)/1440 1440 being the number of minutes in a day. Richard "ascottbag-hcm" wrote in message ... I am receiving a report from a vendor that lists call time minutes of usage in decimal form, i.e. 5.15. When I try to convert this figure into actual time format using the formula, =TEXT(G36/24,"H:MM:SS"), my return is 5:09:00. It appears this formula is returning the amount of time after midnite and not what I am looking for, which is the time expressed in actual hours and or minutes used. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to convert time expressed as a decimal to actual hours or minu
Ok here is my guess. 5.15 minutes is equal to 5 minutes and 9 seconds. I
would use =G36/1440 and format as mm:ss. The answer = 05:09 "Peo Sjoblom" wrote in message ... Yes you are correct, sometimes I read questions too fast. I just went with the what the OP tried with his TEXT formula. -- Regards, Peo Sjoblom "Richard Schollar" wrote in message ... Peo As my reply asked, it depends on what is represented by the .15. If it means 15/100 of a minute then your formula is indeed the correct one. Richard "Peo Sjoblom" wrote in message ... Why not just =A1/1440 -- Regards, Peo Sjoblom "Richard Schollar" wrote in message ... Hi Is 5.15 equivalent to 5 minutes and 15 seconds? If so, you could use the following to convert to Excel time (remember to format the formula cell in a time format): =(A1+MOD(A1,1)*40/60)/1440 1440 being the number of minutes in a day. Richard "ascottbag-hcm" wrote in message ... I am receiving a report from a vendor that lists call time minutes of usage in decimal form, i.e. 5.15. When I try to convert this figure into actual time format using the formula, =TEXT(G36/24,"H:MM:SS"), my return is 5:09:00. It appears this formula is returning the amount of time after midnite and not what I am looking for, which is the time expressed in actual hours and or minutes used. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to convert time expressed as a decimal to actual hours or
Hi Richard,
I know this was posted a long time ago, but I have a simular problem only with hours and minutes. I need to convert numbers like 33.33 to an hour:minute signature. Can you help me with this? Thanks much Jacqueline "Richard Schollar" wrote: Hi Is 5.15 equivalent to 5 minutes and 15 seconds? If so, you could use the following to convert to Excel time (remember to format the formula cell in a time format): =(A1+MOD(A1,1)*40/60)/1440 1440 being the number of minutes in a day. Richard "ascottbag-hcm" wrote in message ... I am receiving a report from a vendor that lists call time minutes of usage in decimal form, i.e. 5.15. When I try to convert this figure into actual time format using the formula, =TEXT(G36/24,"H:MM:SS"), my return is 5:09:00. It appears this formula is returning the amount of time after midnite and not what I am looking for, which is the time expressed in actual hours and or minutes used. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to convert time expressed as a decimal to actual hours or
Jacqueline,
If you need it to be in text, just use your formula from your original post: =TEXT(G36/24,"H:MM:SS") But, if you want to use the time in caculations, simplify it like this: =G36/24 ....then format the cell with one of the time formats. HTH, Conan Kelly "Jacqueline" wrote in message ... Hi Richard, I know this was posted a long time ago, but I have a simular problem only with hours and minutes. I need to convert numbers like 33.33 to an hour:minute signature. Can you help me with this? Thanks much Jacqueline "Richard Schollar" wrote: Hi Is 5.15 equivalent to 5 minutes and 15 seconds? If so, you could use the following to convert to Excel time (remember to format the formula cell in a time format): =(A1+MOD(A1,1)*40/60)/1440 1440 being the number of minutes in a day. Richard "ascottbag-hcm" wrote in message ... I am receiving a report from a vendor that lists call time minutes of usage in decimal form, i.e. 5.15. When I try to convert this figure into actual time format using the formula, =TEXT(G36/24,"H:MM:SS"), my return is 5:09:00. It appears this formula is returning the amount of time after midnite and not what I am looking for, which is the time expressed in actual hours and or minutes used. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to convert time expressed as a decimal to actual hours or
Thanks, this worked :)
"Conan Kelly" wrote: Jacqueline, If you need it to be in text, just use your formula from your original post: =TEXT(G36/24,"H:MM:SS") But, if you want to use the time in caculations, simplify it like this: =G36/24 ....then format the cell with one of the time formats. HTH, Conan Kelly "Jacqueline" wrote in message ... Hi Richard, I know this was posted a long time ago, but I have a simular problem only with hours and minutes. I need to convert numbers like 33.33 to an hour:minute signature. Can you help me with this? Thanks much Jacqueline "Richard Schollar" wrote: Hi Is 5.15 equivalent to 5 minutes and 15 seconds? If so, you could use the following to convert to Excel time (remember to format the formula cell in a time format): =(A1+MOD(A1,1)*40/60)/1440 1440 being the number of minutes in a day. Richard "ascottbag-hcm" wrote in message ... I am receiving a report from a vendor that lists call time minutes of usage in decimal form, i.e. 5.15. When I try to convert this figure into actual time format using the formula, =TEXT(G36/24,"H:MM:SS"), my return is 5:09:00. It appears this formula is returning the amount of time after midnite and not what I am looking for, which is the time expressed in actual hours and or minutes used. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert 29.08 hours (shown in decimal form) to time shown in "hh:m | Excel Worksheet Functions | |||
| How do I convert more than 24 hours time to decimal? | Excel Discussion (Misc queries) | |||
| Convert dd:hr:mn:ss to Decimal number of hours | Excel Discussion (Misc queries) | |||
| how to add hours and show actual hours not decimal numbers | New Users to Excel | |||
| Convert Hours to Decimal | Excel Worksheet Functions | |||
Linear Mode
