SOLVED

Convert Time Text to Hours

Copper 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

@KWalsh07 

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 (Copper Contributor)
Solution

@KWalsh07 

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

1 best response

Accepted Solutions
best response confirmed by KWalsh07 (Copper Contributor)
Solution

@KWalsh07 

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

View solution in original post