Forum Discussion
Convert Time Text to Hours
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!
Try this version:
=24*TIMEVALUE(IF(ISNUMBER(FIND("hour",D2)),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"s","")," hour ",":")," minute",":00"),"0:"&SUBSTITUTE(SUBSTITUTE(D2,"s","")," minute",":00")))
4 Replies
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.
- HansVogelaar How would modify that formula to automatically account for entries that read less that one hour, and more than 2 hours, (Ex. 57 minutes, or 3 hours 27 minutes)
Try this version:
=24*TIMEVALUE(IF(ISNUMBER(FIND("hour",D2)),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"s","")," hour ",":")," minute",":00"),"0:"&SUBSTITUTE(SUBSTITUTE(D2,"s","")," minute",":00")))
Outstanding, thank you so much. You nailed it right on the head.
