I have an URL to a CSV file which I would like to import to google spreadsheets. I am using IMPORTDATA function :
=IMPORTDATA(B21)
You can see the doc in here: spreadsheed link
My csv file looks like :
2023-06-14,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
2023-06-17,3,58,0,87,43,1,0,1,3,2,7,0,2,5,2,3,9,0,4,6,4,23,17
2023-06-24,7,185,0,263,123,6,1,1,8,7,16,1,7,16,7,5,15,2,7,8,7,80,31
...
The first column is a date. When the CSV is presented in the spreadsheet the dates from the first column are transformed into integer numbers:
45091
45094
45101
The whole file google spreadsheet looks like:
I have no clue how this numbers relate to the given dates, if I knew I could use some formula to recalculate them into proper dates. I have tried also to use query
=QUERY(IMPORTDATA( ), "Select * ")
But this did not solve my issue. How to import the dates correctly as they are?
Instead of formatting you can also use another trick:
=text(B2, "yyyy-mm-dd")
I can recommend to insert a new column before your data (so use importdata formula from column B). In column A you use header "Clean Date" and use the formula:
=arrayformula(if(B2:B<>"", text(B2:B, "yyyy-mm-dd"), ""))
In this case the formula first checks if there is a value in the row of column B. If there is a value there it will perform the text formula. The arrayformula makes sure that this formula will be performed over the whole column so you don't have to add a formula in every row (makes your sheet faster and it will contain less errors).