0

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

google drive csv preview

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

google sheets view of dates

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

google sheets trying to convert to proper dates

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

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.

0

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)

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.