Convert Time Text to Hours

New Contributor

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!

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.

@Hans Vogelaar 

Outstanding, thank you so much. You nailed it right on the head.

@Hans Vogelaar 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)
best response confirmed by KWalsh07 (New Contributor)


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")))