Google-sheets – Google Sheets interpreting dates from CSV in a weird way

csvgoogle sheetsgoogle-sheets-datesgoogle-sheets-import-csv

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?

Best Answer

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.

Related Solutions

Google-sheets – No force-reload tip on Google Sheets

i would suspect that setting the expire-time for the .csv file could help. that would be something for the server to deliver.

OR you can trick the webbrowser by appending something random at the end of the url you are using to fetching the data (see my comment on your question):

http://example.com/myfile.csv?_invalidate_cache_of_browser_XYZ

and then you have to put some random XYZ into the url, maybe by usuing the rand() function.

Google-sheets – way to upload a .csv file to Google Docs using a Google Sheets as a template

You can use the importData("URL") function to achieve your goal.

Upload the CSV file somewhere that you can have public access to (I suggest Dropbox or Ge.tt) and you'll endup with something like http://example.com/foo.csv
Go the the first cell that you want to insert your data and write: =importData("http://example.com/foo.csv")

Best Answer

Related Solutions

Google-sheets – No force-reload tip on Google Sheets

Google-sheets – way to upload a .csv file to Google Docs using a Google Sheets as a template

Related Topic