In my Google spreadsheet, I have a column G of dates that interviews are/have been scheduled on. There is a validation set on it to validate for dates with a warning. Every once in a while, when someone was adding historical data but didn't know the right date they just put an x that the event has occurred (or cancelled if it was cancelled, or a variety of other non-blank statuses).

I'm now trying to use Query to get for all rows where that column is empty (regardless of validation)

I have

=QUERY('Interview Planning'!A:G, "select D, A, F, G WHERE G IS NULL")

But seem to be getting back any row for which G is not a valid date, not just where it is NULL.

How do I make it ignore the validation?

Demo of the issue: https://docs.google.com/spreadsheets/d/14HmBvzjjo_z88WtfKAwYckIUNruTA8ivZEUaefKj2qw/edit?usp=sharing

  • Convert G column format to plain text
    – TheMaster
    Jul 25, 2018 at 21:50
  • Then I lose the date validation and I no longer get a calendar picker...unless there's some way to do that in-query? Jul 25, 2018 at 22:15
  • 1
    =arrayformula(QUERY('Interview Planning'!A:G&"", "select Col4, Col1, Col6, Col7 WHERE Col7 IS NULL"))
    – TheMaster
    Jul 25, 2018 at 23:08

1 Answer 1


try this:

=QUERY('Interview Planning'!A:G; "select D, A, F, G where G = ''"; 1)

and also select column G end set it as plain text


  • 1
    What is the ; 0? When I changed it to G = '' then it stopped showing any rows Jul 25, 2018 at 3:08
  • @GeorgeMauer ; 0 means "header of query disabled". probably you need american notation... try this: =QUERY('Interview Planning'!A:G, "select D, A, F, G where G = ''", 0)
    – user0
    Jul 25, 2018 at 6:42
  • Yeah, I saw that in the docs after doing it. Still doesn't work....when I change it to this then none of the results come up Jul 25, 2018 at 14:43
  • @GeorgeMauer well, its hard to help you if I am in the dark. can you share a copy of your sheet?
    – user0
    Jul 25, 2018 at 18:59
  • 1
    Aha! Thank you. No clue at all why that works, but it does. IS NULL works in that case too Jul 26, 2018 at 23:15

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.