2

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!

helcode
  • 1,859
  • 1
  • 13
  • 32
Danielb1212
  • 23
  • 3
  • 1
    Use left() to get the hours and then mid() to get the minutes which you divide by 60 to turn into a decimal then add to get the result. – Solar Mike Jul 03 '18 at 19:40
  • see this then multiply the output by 24: https://stackoverflow.com/questions/36292174/converting-time-formats-in-excel – Scott Craner Jul 03 '18 at 19:43

4 Answers4

1

An alternative formula:

=24*TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A1,"m",""),"h ",":"))
Olly
  • 7,749
  • 1
  • 19
  • 38
1

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

helcode
  • 1,859
  • 1
  • 13
  • 32
0

I am not totally sure what you mean by "input" and whether or not this is in a cell or a data source of some kind that you are parsing and it is unclear whether the "06h 30m" described is a time value in a cell with a custom format or if it is a text field.

This answer assumes that the data is a text field (in cell A2) and that you want to return the number as hours and fractions of an hour with a maximum of 24 hours (since I am also assuming that these are employee hours in a day). It assumes that there is always a two-digit number of hours or minutes and that there is a single space between the xxh and the yym:

=TIME(LEFT(A2,2),MID(A2,5,2),0)*24

I hope this at least leads you in the direction you are trying to get to.

FocusWiz
  • 523
  • 1
  • 3
  • 10
0
Function ConvertTime(hours, minutes)
     return hours + (minutes/60)
End Function
Brad
  • 272
  • 2
  • 7
  • 22