coverting time text string to actual time

New Contributor



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)


For example:








Format as time, then fill down.

Thank you,
That worked perfectly!!!!!!
Thank you sooooo much!