1

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 '18 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? – George Mauer Jul 25 '18 at 22:15
  • 1
    =arrayformula(QUERY('Interview Planning'!A:G&"", "select Col4, Col1, Col6, Col7 WHERE Col7 IS NULL")) – TheMaster Jul 25 '18 at 23:08
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

https://docs.google.com/spreadsheets/d/

  • 1
    What is the ; 0? When I changed it to G = '' then it stopped showing any rows – George Mauer Jul 25 '18 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 '18 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 – George Mauer Jul 25 '18 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 '18 at 18:59
  • 1
    Aha! Thank you. No clue at all why that works, but it does. IS NULL works in that case too – George Mauer Jul 26 '18 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.