Converting Text Time to HH:MM

[kimicoy]

I am working on a project and need help with a formula to convert the following example texts to hh:mm. Any help provided would be greatly appreciated!!

53d 15h 35m 10d 14m 387h 16m 16h 35m <tbody> </tbody> <tbody> </tbody> 28m 1014h 28m <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

<tbody>
</tbody>

 

[Snakehips]

kimicoy,

Welcome to MrExcel.

Maybe you can use the following UDF?
In the vb editor, insert a module and copy in the below code.

Function HrsMin(Rng As Range)
Arry = Split(Trim(Rng), " ")
For c = 0 To UBound(Arry)
    Select Case Right(Arry(c), 1)
        Case "d"
        Tot = Tot + Val(Left(Arry(c), Len(Arry(c)) - 1))
        Case "h"
        Tot = Tot + Val(Arry(c)) / 24
        Case "m"
        Tot = Tot + Val(Arry(c)) / 24 / 60
    End Select


HrsMin = Tot
Next c
End Function

Then format the formula cells as custom [hh]:mm

Excel 2007
	A	B
1	53d 15h 35m	1287:35
2	10d 14m	240:14
3	387h 16m	387:16
4	16h 35m	16:35
5	28m	00:28
6	1014h 28m	1014:28
Sheet14

Cell Formulas
Range	Formula
B1	=HrsMin(A1)

Hope that helps.

 

[AlphaFrog]

Hi and welcome to the forum.

Could you give examples of the end result you expect?

It's not clear if you want to strip the "day" values and have
53d 15h 35m = 15:35

Or this where 53 days is multiplied by 24 hours and added as hours.
53d 15h 35m = 1287:35

 

[kimicoy]

It is the second that you described that I'm looking for ...

Where 53 days is multiplied by 24 hours and added as hours.
53d 15h 35m = 1287:35

Trying the VB option now, but if there's a different suggestion please let me know

 

[kimicoy]

Thanks so much Tony! This worked perfectly!!

kimicoy,

Welcome to MrExcel.

Maybe you can use the following UDF?
In the vb editor, insert a module and copy in the below code.

Function HrsMin(Rng As Range)
Arry = Split(Trim(Rng), " ")
For c = 0 To UBound(Arry)
    Select Case Right(Arry(c), 1)
        Case "d"
        Tot = Tot + Val(Left(Arry(c), Len(Arry(c)) - 1))
        Case "h"
        Tot = Tot + Val(Arry(c)) / 24
        Case "m"
        Tot = Tot + Val(Arry(c)) / 24 / 60
    End Select


HrsMin = Tot
Next c
End Function

Then format the formula cells as custom [hh]:mm


Excel 2007

	A	B
1	53d 15h 35m	1287:35
2	10d 14m	240:14
3	387h 16m	387:16
4	16h 35m	16:35
5	28m	00:28
6	1014h 28m	1014:28

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet14​

Worksheet Formulas Cell Formula B1 =HrsMin(A1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Hope that helps.