excelgoogle-sheetsdata-conversioncustom-functions-excel

Converting "##h ##m" to ##.## hours

My time tracking app exports employee hours in the following format:

xxh yym

I'm looking for a way to convert this into a decimal number of hours. For example,

input(06h 30m)
return(6.5)

Can't figure out a way to do it on Google Sheets or Excel. I tried creating a new function but it's well beyond my ability. I have very little coding experience.

Thanks for any help!

Solution

  • On Google Sheets, you can do this by the REGEXEXTRACT() builtin function as per the following formula (assuming your text is stored at A1)

    =REGEXEXTRACT(A1,"^(\d{2})h \d{2}m") + REGEXEXTRACT(A1,"^\d{2}h (\d{2})m")/60

    REGEXEXTRACT() is a regular expression extractor function. The input text of 06h 30m have the following pattern ^\d{2}h \d{2}m where

    • ^ refers to start of the string
    • \d refers to single digit number, \d{2} means two digits
    • h, , m are all characters as they appear in the reference text.

    Hence, by using () around the portions we need to extract from the text and simple arithmetic you can convert the 06h 30m into 6.5

    More information about regular expression syntax can be found in this tutorial