Apr 19 2021 09:46 AM
Hi There,
I've got about 2700 rows of data. One of the columns was reported in time (ex. 1 hour 6 minutes). I'm looking for a way to convert this General text into Hours (ie. 1.10 hours in the example above).
Thank you in advance!
Apr 19 2021 10:00 AM
Let's say the text values start in D2.
Format E2 as a number with 2 decimal places and enter the following formula in this cell:
=24*TIMEVALUE(SUBSTITUTE(SUBSTITUTE(D2," hour ",":")," minutes",":00"))
Fill down.
Apr 19 2021 12:06 PM
Outstanding, thank you so much. You nailed it right on the head.
Apr 19 2021 12:20 PM
Apr 19 2021 12:35 PM
SolutionTry this version:
=24*TIMEVALUE(IF(ISNUMBER(FIND("hour",D2)),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"s","")," hour ",":")," minute",":00"),"0:"&SUBSTITUTE(SUBSTITUTE(D2,"s","")," minute",":00")))
Apr 19 2021 12:35 PM
SolutionTry this version:
=24*TIMEVALUE(IF(ISNUMBER(FIND("hour",D2)),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"s","")," hour ",":")," minute",":00"),"0:"&SUBSTITUTE(SUBSTITUTE(D2,"s","")," minute",":00")))