4

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.

1

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.

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.