Google-sheets – IMPORTDATA pulls multiple date format even if cell format is specified

google sheetsgoogle-sheets-datesimportdata

I am pulling weather data for a city from a server (visualcrossing.com) and it's giving me the results in CSV format:

CSV data

Name,Date time,Maximum Temperature,Minimum
Temperature,Temperature,Wind Chill,Heat Index,Chance Precipitation
(%),Precipitation,Snow,Snow Depth,Wind Speed,Wind
Gust,Visibility,Cloud Cover,Relative Humidity,Conditions "val-d'Or,
QC","05/21/2021",27.8,20.9,24.9,,27.8,66.6,0.0,0.0,0.0,19.2,49.3,24.1,99.9,54.5,"Rain,
Overcast" "val-d'Or,
QC","05/22/2021",20.1,14.7,17.4,,,66.6,1.6,0.0,0.0,11.6,40.7,17.5,95.7,82.3,"Rain,
Overcast" "val-d'Or,
QC","05/23/2021",16.7,4.8,10.2,0.7,,81.0,0.6,0.0,0.0,16.8,51.1,14.8,64.5,66.3,"Rain,
Partially cloudy" "val-d'Or,
QC","05/24/2021",18.8,1.2,10.6,-0.9,,4.8,0.1,0.0,0.0,8.1,33.1,24.1,17.9,52.0,"Clear"
"val-d'Or,
QC","05/25/2021",20.3,12.4,15.5,,,42.9,3.4,0.0,0.0,18.1,55.4,1.3,96.8,82.7,"Overcast"
"val-d'Or,
QC","05/26/2021",19.3,2.7,12.1,-0.5,,52.4,5.6,0.0,0.0,21.4,55.8,12.2,95.7,80.0,"Rain,
Overcast" "val-d'Or,
QC","05/27/2021",11.8,-2.8,5.1,-6.4,,9.5,0.0,0.0,0.0,11.8,35.6,24.1,37.7,49.9,"Partially cloudy" "val-d'Or,
QC","05/28/2021",18.2,1.8,10.5,-1.0,,9.5,0.0,0.0,0.0,13.6,46.8,24.1,63.2,49.2,"Partially cloudy" "val-d'Or,
QC","05/29/2021",15.3,9.0,11.8,6.4,,33.2,3.2,0.0,0.0,21.0,60.5,16.4,99.8,66.0,"Overcast" "val-d'Or,
QC","05/30/2021",22.7,11.6,14.9,,,38.0,12.9,0.0,0.0,14.0,50.4,1.2,84.4,87.3,"Overcast"
"val-d'Or,
QC","05/31/2021",22.0,8.3,16.0,6.1,,23.7,0.1,0.0,0.0,13.1,33.8,24.1,29.7,68.8,"Partially cloudy" "val-d'Or,
QC","06/01/2021",17.1,6.0,10.9,3.8,,23.7,0.0,0.0,0.0,10.4,25.2,24.1,43.7,64.9,"Partially cloudy" "val-d'Or,
QC","06/02/2021",21.7,5.1,13.7,6.2,,47.5,0.0,0.0,0.0,5.7,10.4,24.1,28.7,56.0,"Partially cloudy" "val-d'Or,
QC","06/03/2021",23.0,9.2,15.3,8.0,,38.0,3.0,0.0,0.0,8.5,24.5,12.8,100.0,63.4,"Overcast" "val-d'Or,
QC","06/04/2021",21.5,14.7,18.0,,,33.2,2.0,0.0,0.0,6.4,16.2,14.2,69.0,84.9,"Partially
cloudy" "val-d'Or,
QC","06/05/2021",26.7,14.3,20.9,,,23.7,0.1,0.0,0.0,7.7,18.0,24.1,48.9,77.3,"Partially
cloudy"

But when I import this into Google Sheets with the IMPORTDATA() function, I have mixed date formats. Changing the cell format to custom or to a different type does not work. Note the 2nd column for the month of May.

Data

As if this isn't weird enough, here is the result when I select the data and switch format to Text only:

Data in text format

I am stuck not being able to use the data in the first few lines because I can't parse it as a Date or as Text.

I've also tried changing the number format to Date and Time, and I get a time for the June entries, but nothing changes for the May entries.

enter image description here

I could use LEFT, RIGHT, MID but then I never know how the data will appear.

Any suggestions?

I feel I need to add that Google Sheets is set to display in French. But I don't really care in which format the date appears, I am simply using the data on other sheets where I will ensure the dates are properly rendered.

Best Answer

If dates in the data are in the mm/dd/yyyy format, you should use importdata() in a spreadsheet whose locale uses the same default date format, e.g. United States. You can then use importrange() to copy the data into another spreadsheet that uses your preferred locale and date format.

The integers you show in the screenshot are dateserial values. They represent values that happen to be valid dates in your current locale, but their values will be incorrect because they have been interpreted incorrectly. The values that look like dates are not dates but text strings that look like dates. They have not been converted to dates at all.

See this answer for an explanation of how date and time values work in spreadsheets.

Related Topic