Convert formatted hours to rounded decimal hours

Question

What I want is this:

These numbers are extracted from a script that I run on Selenium IDE. The problem is that those numbers come as text.

What I need is to convert, for example, "56h 29m" to "56.5" ... "27.75h" to "27.75".

Some of them are easy (like the first 2 columns), I just remove the last letter "h" and it's converted to number...the problem is when the numbers have this "h" in the middle with a space and the "m" at the end (last column).

Is there a way to implement this?

score 1 · Accepted Answer · answered Jan 03 '17 at 15:51

1

You need to break the hours and minutes apart so the minutes can be divided by 60. I am sure there is an easier way, but this works. It assumes your data is in Sheet1 starting in A2. Put the formula in another sheet (like Sheet2 A2) and copy down:

=IFERROR(if(right(Sheet1!A2,1)="h",regexreplace( Sheet1!A2, "[a-z]+" , "" ),concatenate(index(SPLIT(regexreplace(  Sheet1!A2, "[a-z]+" , "" ), " "), 0, 1),replace(round(index(SPLIT(regexreplace(  Sheet1!A2, "[a-z]+" , "" ), " "), 0, 2)/60,1),1,1,""))))

answered Jan 03 '17 at 15:51

Ed Nelson

10,015
2
27
29

Yup! totally forgot about it hehe ... I will use your way and try to find an easier way later, thanks for poiting that! – ryoishikawa74 Jan 03 '17 at 16:47
If that works for you, please approve the answer. Thanks. – Ed Nelson Jan 03 '17 at 16:55

Convert formatted hours to rounded decimal hours

1 Answers1