0

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:

enter image description here

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?

fascynacja
  • 1,625
  • 4
  • 17
  • 35

1 Answers1

0

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).

Sami Chouchane
  • 195
  • 1
  • 11
  • you mean to add this formula to every cell of column A? or there is some trick to magically aply this formula to whole column A automatically? – fascynacja Jun 29 '23 at 09:30
  • 1
    If you add this formula in cell A2 it will automatically be executed over every row :) Just put it in A2 and you will find out (hopefully). – Sami Chouchane Jun 29 '23 at 09:47