Sep 18 2021 06:54 AM
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.
Sep 18 2021 07:59 AM - edited Sep 18 2021 08:00 AM
SolutionFor 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.
Sep 18 2021 08:57 AM
Sep 18 2021 07:59 AM - edited Sep 18 2021 08:00 AM
SolutionFor 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.