Google-sheets – ImportRange not pulling in Dates from first sheet in correct format

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryimportrange

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?

Best Answer

  • 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")

Related Topic