Google-sheets – Time not formatting as expected in Google Sheets

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-datestext formatting

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.

Best Answer

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

Solution

Suppose total distance is in column C1 and total time is in D1 then average speed formula would be:

=C1/(int(D1)*24+hour(D1)+minute(D1)/60+second(D1)/3600)

int function is the one I was after.

Google-sheets – Formatting Google Sheet Dates Inconsistent

Spreadsheet

The spreadsheet demo locale linked on the question is set to United States which use month-day-year sequence for dates. The value shown on the video is 25/03/2015 as the month valid values are from 1 to 12, it can´t be interpreted as a date value on the original spreadsheet.

It's very likely that the second spreadsheet, that converts the 25/03/2015 to date, has a locale that use day-month-year sequence for dates.

Looks that we can't be sure that "ambiguous" strings like 4/3/2015 are being converted to the correct date.

Google Forms

Google Forms responses could be exported as a CSV file from the Google Form. At this time dates are being included in the CSV file as "yyyy-mm-dd", so could be more reliable to use the exported CSV file than the responses sent directly to a the original spreadsheet.

See View and manage form responses for details about how to download forms responses al CSV file.

Best Answer

Related Solutions

Google-sheets – Parsing Time data in Google Spreadsheets

Solution

Google-sheets – Formatting Google Sheet Dates Inconsistent

Spreadsheet

Google Forms

Related Topic