| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert part of decimal to time (minutes only)
For example I have 39:83. I want to convert to 39:49
I want to only convert the :83 decimal into minutes. I am using the =text(value/24, "h:mm") but this is converting 39:83 to 13.49. Please can anyone help ? Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert part of decimal to time (minutes only)
I don't quite get the data, but use
[h]:mm instead of h:mm -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RachelB" wrote in message ... For example I have 39:83. I want to convert to 39:49 I want to only convert the :83 decimal into minutes. I am using the =text(value/24, "h:mm") but this is converting 39:83 to 13.49. Please can anyone help ? Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert part of decimal to time (minutes only)
This will return 39.50
=INT(A1)&":"&(TEXT((A1-INT(A1))*100*0.6,"00")) This will return 39.49 =INT(A1)&":"&(TEXT(ROUNDDOWN((A1-INT(A1))*100*0.6,0),"00")) Vaya con Dios, Chuck, CABGx3 "RachelB" wrote: For example I have 39:83. I want to convert to 39:49 I want to only convert the :83 decimal into minutes. I am using the =text(value/24, "h:mm") but this is converting 39:83 to 13.49. Please can anyone help ? Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert part of decimal to time (minutes only)
Thank You - although #Value error as demical data is 39:83 not 39.83.
Do I need to convert 39:83 to 39.83 first?? If so, can you help me ? Thanks again Rachel "CLR" wrote: This will return 39.50 =INT(A1)&":"&(TEXT((A1-INT(A1))*100*0.6,"00")) This will return 39.49 =INT(A1)&":"&(TEXT(ROUNDDOWN((A1-INT(A1))*100*0.6,0),"00")) Vaya con Dios, Chuck, CABGx3 "RachelB" wrote: For example I have 39:83. I want to convert to 39:49 I want to only convert the :83 decimal into minutes. I am using the =text(value/24, "h:mm") but this is converting 39:83 to 13.49. Please can anyone help ? Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert part of decimal to time (minutes only)
Thanks Bob
Have tried this but doesn't work - is it my explanation you don't understand? Rachel "Bob Phillips" wrote: I don't quite get the data, but use [h]:mm instead of h:mm -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RachelB" wrote in message ... For example I have 39:83. I want to convert to 39:49 I want to only convert the :83 decimal into minutes. I am using the =text(value/24, "h:mm") but this is converting 39:83 to 13.49. Please can anyone help ? Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert part of decimal to time (minutes only)
Sorry, my bad........can't read yet this morning without coffee.....
This one will work with the colon, even with 3-digit hours =LEFT(A1,FIND(":",A1,1))&ROUNDDOWN(MID(A1,FIND(":" ,A1,1)+1,99)*0.6,0) Vaya con Dios, Chuck, CABGx3 "RachelB" wrote: Thank You - although #Value error as demical data is 39:83 not 39.83. Do I need to convert 39:83 to 39.83 first?? If so, can you help me ? Thanks again Rachel "CLR" wrote: This will return 39.50 =INT(A1)&":"&(TEXT((A1-INT(A1))*100*0.6,"00")) This will return 39.49 =INT(A1)&":"&(TEXT(ROUNDDOWN((A1-INT(A1))*100*0.6,0),"00")) Vaya con Dios, Chuck, CABGx3 "RachelB" wrote: For example I have 39:83. I want to convert to 39:49 I want to only convert the :83 decimal into minutes. I am using the =text(value/24, "h:mm") but this is converting 39:83 to 13.49. Please can anyone help ? Thanks |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert part of decimal to time (minutes only)
No it was the conversion of the string you seem to effectr. Try this
=TEXT(SUBSTITUTE(A1,":",".")/24,"[h]:mm") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RachelB" wrote in message ... Thanks Bob Have tried this but doesn't work - is it my explanation you don't understand? Rachel "Bob Phillips" wrote: I don't quite get the data, but use [h]:mm instead of h:mm -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RachelB" wrote in message ... For example I have 39:83. I want to convert to 39:49 I want to only convert the :83 decimal into minutes. I am using the =text(value/24, "h:mm") but this is converting 39:83 to 13.49. Please can anyone help ? Thanks |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert part of decimal to time (minutes only)
Fantastic - thank you very much.
Happy Christmas. Rachel "CLR" wrote: Sorry, my bad........can't read yet this morning without coffee..... This one will work with the colon, even with 3-digit hours =LEFT(A1,FIND(":",A1,1))&ROUNDDOWN(MID(A1,FIND(":" ,A1,1)+1,99)*0.6,0) Vaya con Dios, Chuck, CABGx3 "RachelB" wrote: Thank You - although #Value error as demical data is 39:83 not 39.83. Do I need to convert 39:83 to 39.83 first?? If so, can you help me ? Thanks again Rachel "CLR" wrote: This will return 39.50 =INT(A1)&":"&(TEXT((A1-INT(A1))*100*0.6,"00")) This will return 39.49 =INT(A1)&":"&(TEXT(ROUNDDOWN((A1-INT(A1))*100*0.6,0),"00")) Vaya con Dios, Chuck, CABGx3 "RachelB" wrote: For example I have 39:83. I want to convert to 39:49 I want to only convert the :83 decimal into minutes. I am using the =text(value/24, "h:mm") but this is converting 39:83 to 13.49. Please can anyone help ? Thanks |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert part of decimal to time (minutes only)
Maybe you need this (provided that hours and minutes are always two digit
long): =LEFT(A1,2)*(1/24)+(RIGHT(A1,2)/100)*(1/24) and format the result cell like [h]:mm Regards, Stefi RachelB ezt *rta: For example I have 39:83. I want to convert to 39:49 I want to only convert the :83 decimal into minutes. I am using the =text(value/24, "h:mm") but this is converting 39:83 to 13.49. Please can anyone help ? Thanks |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert part of decimal to time (minutes only)
You're welcome, thanks for the feedback.
Merry Christmas to you and yours as well......... Vaya con Dios, Chuck, CABGx3 "RachelB" wrote: Fantastic - thank you very much. Happy Christmas. Rachel "CLR" wrote: Sorry, my bad........can't read yet this morning without coffee..... This one will work with the colon, even with 3-digit hours =LEFT(A1,FIND(":",A1,1))&ROUNDDOWN(MID(A1,FIND(":" ,A1,1)+1,99)*0.6,0) Vaya con Dios, Chuck, CABGx3 "RachelB" wrote: Thank You - although #Value error as demical data is 39:83 not 39.83. Do I need to convert 39:83 to 39.83 first?? If so, can you help me ? Thanks again Rachel "CLR" wrote: This will return 39.50 =INT(A1)&":"&(TEXT((A1-INT(A1))*100*0.6,"00")) This will return 39.49 =INT(A1)&":"&(TEXT(ROUNDDOWN((A1-INT(A1))*100*0.6,0),"00")) Vaya con Dios, Chuck, CABGx3 "RachelB" wrote: For example I have 39:83. I want to convert to 39:49 I want to only convert the :83 decimal into minutes. I am using the =text(value/24, "h:mm") but this is converting 39:83 to 13.49. Please can anyone help ? Thanks |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert part of decimal to time (minutes only)
Great - that works - thanks very much !
"Bob Phillips" wrote: No it was the conversion of the string you seem to effectr. Try this =TEXT(SUBSTITUTE(A1,":",".")/24,"[h]:mm") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RachelB" wrote in message ... Thanks Bob Have tried this but doesn't work - is it my explanation you don't understand? Rachel "Bob Phillips" wrote: I don't quite get the data, but use [h]:mm instead of h:mm -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RachelB" wrote in message ... For example I have 39:83. I want to convert to 39:49 I want to only convert the :83 decimal into minutes. I am using the =text(value/24, "h:mm") but this is converting 39:83 to 13.49. Please can anyone help ? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| convert hours format to decimal or minutes (exp. 1:15:00 = 75 min. | Excel Worksheet Functions | |||
| Convert Decimal hours and Minutes to minutes please. | Excel Worksheet Functions | |||
| Convert degrees at 'Minutes' format. i.e. 43° 22', to Decimal | Excel Discussion (Misc queries) | |||
| How do i convert angles from degrees,minutes, seconds to decimal | Excel Discussion (Misc queries) | |||
| convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) | |||
Linear Mode
