0

I'm trying to pull in a date cell from one sheet to another with an importrange function:

=iferror(query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1UClhpev01z-3zl6iyMvVrOSufXpovQO16lZxEqzagEs/edit?usp=sharing", "Agencies!A:H")," Select Col2,Col5, Col7 where lower(Col2) matches'"&lower(TEXTJOIN("*|",true,A4)&"*")&"' ORDER BY Col2 ASC LIMIT 1"),"**no matches found**")

It is pulling in all fields with the correct format except Col5:

0

I tried reformatting the original sheets column with different date settings but that didn't work. I also tried adding in date formatting functions such as toDate(Col5, 'MM/DD/YYYY') but these also didn't work and broke the entire query.

Any insights as to why the importrange function would reformat the specific date column and no others?

0
  • try using TO_TEXT:

=ARRAYFORMULA(IFERROR(QUERY(TO_TEXT(IMPORTRANGE(
 "1UClhpev01z-3zl6iyMvVrOSufXpovQO16lZxEqzagEs", "Agencies!A:H")),
 "select Col2,Col5,Col7 
  where lower(Col2) matches'"&LOWER(TEXTJOIN("|", 1, A4)&"")&"' 
  order by Col2 asc 
  limit 1"), 
 "no matches found"))

  • also you can use format in QUERY:

=IFERROR(QUERY(QUERY(IMPORTRANGE(
 "1UClhpev01z-3zl6iyMvVrOSufXpovQO16lZxEqzagEs", "Agencies!A:H"),
 "select Col2,Col5,Col7 
  where lower(Col2) matches'"&LOWER(TEXTJOIN("|", 1, A4)&"")&"' 
  order by Col2 asc 
  limit 1"),
 "format Col2 'mm/dd/yyyy'"), 
 "no matches found")

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.