I'm importing a HTML table into my Google sheet with IMPORTHTML. I need to parse date strings to real dates in the first column so I can format them and sort them properly. The table structure is as follows:

"header1"       |"header2" |"header3" |"header4"
"4.6.2020 09:00"|"data"    |"data"    |"data"
"4.6.2020 10:30"|"data"    |"data"    |"data"
"4.6.2020 10:30"|"data"    |"data"    |"data"
"4.6.2020 11:00"|"data"    |"data"    |"data"

So basically a header row followed by data rows where dates are string values in the first column and formatted as described. Simplest solution I could think of is to extend a parsing formula in a new column next to the data table, like:

=DATE(LEFT(INDEX(SPLIT(A2; "."); 0; 3); 4); INDEX(SPLIT(A2; "."); 0; 2); INDEX(SPLIT(
A2; "."); 1)) + TIME(INDEX(SPLIT(INDEX(SPLIT(A2; " "); 2); ":"); 1); INDEX(SPLIT(INDEX(SPLIT(A2; " "); 2); ":"); 2); 0)

Works fine. However I was wondering if this could be done without adding the extra column so that the existing date strings starting in A2 would be replaced with real dates. I started by separating the header row and the date column with an array literal:

QUERY(IMPORTHTML(url; "table"; 2); "select Col1, Col2, Col3, Col4 limit 1"; 0); 
QUERY(IMPORTHTML(url; "table"; 2); "select Col1 offset 1"; 0)\                     <-- Date column
QUERY(IMPORTHTML(url; "table"; 2); "select Col2, Col3, Col4 offset 1"; 0)

And then I suppose I should somehow apply the DATE formula I wrote to the second query? This is where I stalled. I tried to replace the query with the DATE formula, then the cell references with the second QUERY formula, and finally wrapping it into an ARRAYFORMULA. Not only did it not work, but it became really cumbersome, because of several IMPORTHTML calls in the date parser. Can this be done in a way that's more readable, has less imports and is easier to maintain? Do I need to write a custom function perhaps?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.