Google-sheets – Formatting Google Sheet Dates Inconsistent

formattinggoogle sheetsgoogle-sheets-dates

I have a sheet where various data is entered through a form. Dates are picked on the form using the standard date picker provided by Google Forms.

Some dates in a column I can change the date format but others don't.

What is odd is that when I copy this column into a new sheet and then try and change the format these stubborn ones then change correctly.

I have made a copy of the sheet (editable) and removed private info and left the column in question. BTW: there are two other date columns and they have similar issues. The issues are not on the same row for most cases. So it's not row related.

Here's a short video where I show how I try to change the date format on the "bad" sheet, then I copied the culprit date entry to another sheet and then I can format it.
I hope you can assist with a way to work around this bug.

Best Answer

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.

Related Topic