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?
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?