I imported a report to Google Sheets and the duration column shows 7 h 37 m or 14 h 5 m a similar format.

When I replace h with : it still does not properly format to time duration and so I cannot average the time in total.

It appears oddly where some are formatted but others are not or look non-uniform.

Formatted Result

  • Add to the question the values that aren't converted properly as text not as image. – Rubén May 25 '19 at 15:34
  • I wonder does the fact that google sees your data as partially text and partially numbers have anything to do with your troubles. This is evident by some of your data being on the right of the cell and some on the left. Check the formulas that provided the data to these cells to determine what google sheets classified it as. Did the original sheets formulas import with the sheet or is the data simply values. If you do not need the formulas that originally created the values in the cells then simply copy and paste to get rid of formulas if they exist. If not or if they do and you have stripped – XCATHADOR May 26 '19 at 6:42
  • If not or if they do and you have stripped them go to format section and choose a date format for that cell. If conditional formatting has been selected you may have to clear formatting first. – XCATHADOR May 26 '19 at 6:42

=ARRAYFORMULA(TEXT(SUBSTITUTE(SUBSTITUTE(A2:A, " h ", ":"), " m", ":00"), "[h]:mm"))



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.