Google Sheets interpreting dates from CSV in a weird way

Question

I'm opening a csv report in Google Sheets. The Google Drive preview shows the dates like this:

When I open this file in Google Sheets the dates now look like this:

And if I try and convert the date to int and then date it works for some but not all:

Any idea how I can extract just the date from these cells?

Answer 1

Ok I've worked it out. Google Sheets imports the date cell as text and then interprets that cell as a date. The Locale is set as UK so it can't handle the US format date. I had change the spreadsheet Locale to US, use the =TO_DATE(INT(A2)) formula and then create a custom format on that column to show the date in UK format.

Answer 2

Most likely your data is mixed or some cells have been formatted before entering your data.

Debugging

1. Use the ISNUMBER function to check wether all your dates are correct.
2. Go to the top menu and Format > Number > Automatic and reformat your cells.

(If you still have issues, please share a sample sheet)