3

I understand that the results of a query will be of whatever data type majorly dominates a column of data, and the rest will be set to null and appear blank.

Is there any sort of workaround for this, particularly with dates?

My query needs to pull dates, but also include strings.

| improve this question | |
  • I actually figured it out by adjusting my source data to plain text. By doing so, the query function didn't assume a particular data type. Instead, it interpreted all inputs as strings, and displayed all data correctly. Thank you. – Roger Comstock May 19 '17 at 20:34
2

You can coerce all values to be strings on the fly, without modifying the spreadsheet, by using arrayformula(your_range & ""):

=query(arrayformula(A2:B10 & ""), "select Col1, Col2", 0)

Concerning the dates, note that internally they are represented as the number of days since December 30, 1899: for example, March 4, 2015 is 42770. So the query given above will get "42770" instead of what you might expect. This might be difficult to use for anything.

Specifically for date-to-string conversion, the following arrayformula works better (put it inside of the query as above):

arrayformula(iferror(text(A2:B10, "yyyy-MM-dd"), A2:B10))

The dates will now appear "2015-03-04" instead of "42770".

Presumably, you will want to return to numbers at some point, after the query did its job. Another arrayformula can do that: 

=arrayformula(iferror(value(C2:D10), C2:D10))

And the date appearance will be restored if the output of the arrayformula is formatted as Date.

| improve this answer | |
  • Gerry, thank you very much. This will be a viable solution for future challenges I may encounter with the same issue. I really appreciate it. – Roger Comstock May 25 '17 at 16:30

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.