Posts

Showing posts from May, 2017

Use Excel to parse text to hours

Image
I use a tool that displays elapsed time in the format of Xd Xh Xm, for days-hours-and minutes. If it's less than 1 day, then the output is Xh Xm. It's easy for me to scrape the output, but I need it in hours to manipulate it further. Example outputs and converted results: 2d 0h 18m = 48.3 (in hrs) 14h 9m = 14.15 (in hrs) I wrote an Excel function to accomplish this task. Assuming my output is in cell B3, this is the function. =IF( ISERROR (FIND("d",B3)),LEFT(B3,FIND("h",B3)-1)+(MID(B3,FIND(" ",B3,FIND("h",B3))+1,FIND("m",B3)-FIND(" ",B3,FIND("h",B3))-1)/60),( LEFT(B3,FIND("d",B3)-1) *24)+ MID(B3, FIND(" ",B3,FIND("d",B3))+1 ,FIND("h",B3)-FIND(" ",B3,FIND("d",B3))-1) +( MID(B3, FIND(" ",B3,FIND("h",B3))+1 ,FIND("m",B3)-FIND(" ",B3,FIND("h",B3))-1 )/60)) Notice it starts with an IF s