SOLVED

coverting time text string to actual time

%3CLINGO-SUB%20id%3D%22lingo-sub-2763345%22%20slang%3D%22en-US%22%3Ecoverting%20time%20text%20string%20to%20actual%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2763345%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%26nbsp%3B%20lot%20of%20reports%20to%20do.%26nbsp%3B%20There%20is%20a%20column%20in%20my%20spreadsheet%20that%20is%20a%20duration%2C%20and%20shows%20as%20a%20text%20string.%26nbsp%3B%20My%20boss%20wants%20me%20to%20do%20an%20average%20of%20time%2C%26nbsp%3B%20and%20I%20cannot%20figure%20out%20how%20to%20extract%20the%20information%20as%20time.%26nbsp%3B%20The%20problem%20is%20that%20some%20rows%20are%20eg%26nbsp%3B%2029%20m%2031%20s%2C%20and%20others%20show%201%20h%2029%20m%2031%20s.%26nbsp%3B%20%26nbsp%3Bif%20i%20do%20the%20formula%26nbsp%3B%20%3Dleft(z2%2C2)%26nbsp%3B%20it%20extract%20the%20minutes%20(29)%20and%20the%20hour%20(1)%20into%20the%20same%20column%2C%20instead%20of%20extracting%20only%20the%20hour%20in%20the%20rows%20where%20it%20appears.%26nbsp%3B%20%26nbsp%3BI%20have%20attached%20a%20sample%20from%20my%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2763345%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2763473%22%20slang%3D%22en-US%22%3ERe%3A%20coverting%20time%20text%20string%20to%20actual%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2763473%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1159810%22%20target%3D%22_blank%22%3E%40Marie_Jacob%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(TIMEVALUE(IF(ISERROR(FIND(%22h%22%2CI2))%2C%220%3A%22%2C%22%22)%26amp%3BSUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I2%2C%22h%22%2C%22%3A%22)%2C%22m%22%2C%22%3A%22)%2C%22s%22%2C%22%22))%2C%22N%2FA%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(TIME(IFERROR(LEFT(I2%2CFIND(%22h%22%2CI2)-1)%2C0)%2CMID(I2%2CIFERROR(FIND(%22h%22%2CI2)%2C0)%2B1%2CFIND(%22m%22%2CI2)-IFERROR(FIND(%22h%22%2CI2)%2C0)-2)%2CMID(I2%2CFIND(%22m%22%2CI2)%2B1%2CFIND(%22s%22%2CI2)-FIND(%22m%22%2CI2)-1))%2C%22N%2FA%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2763615%22%20slang%3D%22en-US%22%3ERe%3A%20coverting%20time%20text%20string%20to%20actual%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2763615%22%20slang%3D%22en-US%22%3EThank%20you%2C%3CBR%20%2F%3EThat%20worked%20perfectly!!!!!!%3CBR%20%2F%3EThank%20you%20sooooo%20much!%3C%2FLINGO-BODY%3E
New 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 (New 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!