Convert part of decimal to time (minutes only)

[RachelB]

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

 

[Bob Phillips]

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)

 

[CLR]

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]

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

 

[RachelB]

Thanks Bob
Have tried this but doesn't work - is it my explanation you don't understand?
Rachel

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)

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

 

[CLR]

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

 

[Bob Phillips]

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)

Thanks Bob
Have tried this but doesn't work - is it my explanation you don't
understand?
Rachel

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)

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

 

[RachelB]

Fantastic - thank you very much.
Happy Christmas.
Rachel

 

[Stefi]

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:

 

[CLR]

You're welcome, thanks for the feedback.

Merry Christmas to you and yours as well.........

Vaya con Dios,
Chuck, CABGx3

 

[RachelB]

Great - that works - thanks very much !

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)

Thanks Bob
Have tried this but doesn't work - is it my explanation you don't
understand?
Rachel

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)

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