Converting text time to HH:MM format

[jwpickett]

I've got a data field that is represented in text format as XX hrs. XX mins. where the hours can be anywhere from 0 hrs to 120 hrs and the minutes from 1 min. to 59 min.

I've tried using TIME(LEFT(a2,2),MID(a2,FIND("min",a2)-3,2),0) but if more than 24 hrs. it won't convert correctly. For example, 73 hrs. 08 mins. converts to 01:08 but should, be reflected as 73:08.

Any ideas on how I can resolve this?

 

[Richard Schollar]

Try formatting the cell containing your current formula using the following custom format:

[h]:mm

 

[jwpickett]

All that did was change it from 01:08 to 1:08.

 

[Richard Schollar]

Try this:

Excel Workbook
	A	B
1	78 hrs. 08 mins	78:08
2	50 hrs. 56 mins	50:56
3	112 hrs. 35 mins	112:35
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(LEFT(A1,FIND("mins",A1)-1)," hrs. ",":")+0

 

[jwpickett]

yes, yes, yes.

Thanks so much.

 

[pedie]

This formula is great..was juts growing throughh....