How to i change the date time format on Google Sheets?

Question

I am working on a datasheet on Google sheets and I am trying to change values in column (D) to date time format, so I went to Format<number<date time. But only a few values are getting converted and the rest remain the same. Please see below-

Screenshot of column D

The values aligned on right are converted and the left ones aren't. I even tried paint format, trim white spaces but it didnt work. Please suggest a way on how to resolve this error. Also, there are 49623 values in column D.

thank you

Answer 1

Your spreadsheet is set to a locale that uses the mm/dd/yyyy date format, while your data is using the dd/mm/yyyy date format. That means that the dates where the day of the month is less than or equal to 12 will get converted to dates incorrectly, and the rest of the data will remain as text strings.

To make it work, choose File > Settings > Locale and choose a locale that matches your data, then repaste the data to convert it correctly.

The "49623 values" are dateserials that can be converted to dates by formatting them in a date format. See this answer for an explanation of how date and time values work in spreadsheets.