Skip to content
Sheet ErrorSheet ErrorSheet Error

Fix QUERY #VALUE! in Google Sheets (dates mixed with text)

Some cells in column A look like dates and QUERY still returns #VALUE!. The formula is not reading the dates you can see. It is reading the whole column’s type.

Recreate it on a blank sheet

Section titled “Recreate it on a blank sheet”
  1. Open a blank spreadsheet. Leave the tab named Sheet1. In A1 type date, B1 item.
  2. In A2 type 2024/1/15 so Sheets stores a real date. B2 Widget. A3 TBD as plain text. B3 Skip. A4 2/1/2024 as a real date. B4 Gadget. A5 unknown. B5 Thing. A6 pending. B6 Doohickey.
  3. In D1 enter exactly =QUERY(A1:B6,"select year(A), B",1). The cell shows #VALUE!. The tooltip is Unable to parse query string for Function QUERY parameter 2: Can’t perform the function year on a column that is not a Date or a DateTime column.

D1 selected. Formula bar shows =QUERY(A1,“select year(A), B”,1). D1 is #VALUE!. Tooltip says year cannot run on a column that is not a Date or a DateTime column.

where A > date '2024-01-01' is not this recreate. On a short table that filter can succeed and hide the mixed-type problem. The recreate is year(A) on the mixed A column.

What is actually wrong

Section titled “What is actually wrong”
  • QUERY inspects a whole column’s type. Column A mixes real dates with text (TBD, unknown, pending). That mix is not a Date or DateTime column.
  • year(A) only runs on a Date or DateTime column. Mixed type is why the query string fails to parse.
  • This is not a locale / comma-vs-semicolon parse error. This is not IMPORTRANGE select A vs Col1.

Fix

Section titled “Fix”

Do not QUERY the mixed column.

Leave D1 as the broken formula so #VALUE! stays on the sheet.

Helper column: C1 date_clean. In C2 enter =IF(ISNUMBER(A2),A2,"") and fill down through C6. Text cells become blank. Real dates remain.

Then in E1 enter =QUERY({C1:C4,B1:B4},"select Col1, Col2 where Col1 > date '2024-01-01'",1). The formula spills headers plus the two date rows: Widget and Gadget.

E1 selected. Formula bar shows =QUERY({C1,B1},“select Col1, Col2 where Col1 > date ‘2024-01-01’”,1). Spill is date_clean / item with Widget and Gadget. Column C shows cleaned dates and blanks. D1 is still #VALUE!.

Do not wrap this in IMPORTRANGE. Do not change File > Settings locale. Do not try to make the mixed column one QUERY without the helper.

If year fails on a column that looks like dates, look for leftover text in that column first.