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 statement. The IF statement with IS…