Data conversion

[basrirasheed]

Can you let me know how we can change data in the following format to hours,

format that is sent to me is 1d 17h 29m, how can i change this into hours and minutes?

only hours and minutes please.

Regards
Basri

[JBeaucaire]

If that text string is in A1, then this:

=LEFT(A1,FIND("d",A1)-1)*24+MID(A1,FIND(" ",A1)+1,FIND("h",A1)-FIND(" ",A1)-1)&"h" & MID(A1,FIND("h",A1)+1,10)

[teylyn]

Hi,

with A1 showing the text string

1d 17h 29m

you can use

Please Login or Register  to view this content.

and format with custom format "[h]:mm"

The result is 41:29 = 41 hours and 29 minutes.

If you don't want to show anything greater than 24 hours, use

Please Login or Register  to view this content.

and format with custom format "[h]:mm"

The result is 17:29

The above formula works under the assumption that the input format is

xd xh xs
or
xxd xxh xxs

one or two digits per day, hour, minute.

cheers

[friendyousuf]

Hi Excel Master's,

Instead of FIND formula, you can use SEARCH. Find is case sensitive. Use this formula.

=TIME(MID(A1,SEARCH("d",A1)+2,(SEARCH("h",A1)-SEARCH("d",A1))-2),SUBSTITUTE(MID(A1,SEARCH("h",A1)+2,99),"m",""),0)

[basrirasheed]

Hi i did manage to try the formula, but its not taking the formula correctly .


My data is in the H1 coloumn and i did change all A1 to H1, but still no success .


again example of the format is : 1d 17h 29m

thank you for your help in advance.

Regards
Basri

[teylyn]

but its not taking the formula correctly .

post a data sample in a workbook

[basrirasheed]

Hi,

The below mentioned formula is exactly what I am looking for, but need a slight correction in this.

when i give a value for example 1d 17h 29m, the output that i get is 17h 29 min, but its not adding the 1 day to it, when the correct outpu should have been 41:29,

Please help

Regards

Hi Excel Master's,

Instead of FIND formula, you can use SEARCH. Find is case sensitive. Use this formula.

=TIME(MID(A1,SEARCH("d",A1)+2,(SEARCH("h",A1)-SEARCH("d",A1))-2),SUBSTITUTE(MID(A1,SEARCH("h",A1)+2,99),"m",""),0)

[teylyn]

well, read JBeaucaire's and my first post again, then! It's all there for the taking.

The "below mentioned formula" is a copy of my first formula, using SEARCH instead of FIND, but omitting the day part.

Unfortunately, you did not specify in your original post how you want to handle days. In fact, you stated

only hours and minutes please.

So, there you go. Still, I gave you a version that delivers:

The result is 41:29 = 41 hours and 29 minutes.

It's all up there. We took the trouble to provide solutions. Several. Now please take the trouble and read them.