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

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

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

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

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

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

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

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

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

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

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