# Converting Text Time to HH:MM

#### kimicoy

##### New Member
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>

Last edited:

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
kimicoy,

Welcome to MrExcel.

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

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
AB
153d 15h 35m1287:35
210d 14m240:14
3387h 16m387:16
416h 35m16:35
528m00:28
61014h 28m1014:28
Sheet14
Cell Formulas
RangeFormula
B1=HrsMin(A1)

Hope that helps.

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

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

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.

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
AB
153d 15h 35m1287:35
210d 14m240:14
3387h 16m387:16
416h 35m16:35
528m00:28
61014h 28m1014:28

</tbody>
Sheet14

Worksheet Formulas
CellFormula
B1=HrsMin(A1)

</tbody>

<tbody>
</tbody>

Hope that helps.

Replies
2
Views
508
Replies
6
Views
482
Replies
3
Views
933
Replies
3
Views
1K
Replies
3
Views
2K

1,196,358
Messages
6,014,780
Members
441,847
Latest member
hw407

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back