 SOLVED

# 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).

4 Replies

# Re: Convert Time Text to Hours

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.

# Re: Convert Time Text to Hours

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

# Re: Convert Time Text to Hours

@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)
Solution

# Re: Convert Time Text to Hours

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