SOLVED

coverting time text string to actual time

Copper Contributor

Hello,

 

I have  lot of reports to do.  There is a column in my spreadsheet that is a duration, and shows as a text string.  My boss wants me to do an average of time,  and I cannot figure out how to extract the information as time.  The problem is that some rows are eg  29 m 31 s, and others show 1 h 29 m 31 s.   if i do the formula  =left(z2,2)  it extract the minutes (29) and the hour (1) into the same column, instead of extracting only the hour in the rows where it appears.   I have attached a sample from my file.

 

Thank you in advance.

2 Replies
best response confirmed by Marie_Jacob (Copper Contributor)
Solution

@Marie_Jacob 

For example:

 

=IFERROR(TIMEVALUE(IF(ISERROR(FIND("h",I2)),"0:","")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I2,"h",":"),"m",":"),"s","")),"N/A")

 

or

 

=IFERROR(TIME(IFERROR(LEFT(I2,FIND("h",I2)-1),0),MID(I2,IFERROR(FIND("h",I2),0)+1,FIND("m",I2)-IFERROR(FIND("h",I2),0)-2),MID(I2,FIND("m",I2)+1,FIND("s",I2)-FIND("m",I2)-1)),"N/A")

 

Format as time, then fill down.

Thank you,
That worked perfectly!!!!!!
Thank you sooooo much!
1 best response

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

@Marie_Jacob 

For example:

 

=IFERROR(TIMEVALUE(IF(ISERROR(FIND("h",I2)),"0:","")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I2,"h",":"),"m",":"),"s","")),"N/A")

 

or

 

=IFERROR(TIME(IFERROR(LEFT(I2,FIND("h",I2)-1),0),MID(I2,IFERROR(FIND("h",I2),0)+1,FIND("m",I2)-IFERROR(FIND("h",I2),0)-2),MID(I2,FIND("m",I2)+1,FIND("s",I2)-FIND("m",I2)-1)),"N/A")

 

Format as time, then fill down.

View solution in original post