1

I have run into an issue where I have a query formula that acts like a filter on multiple criteria. I have been trying to add into it the ability to search between dates as well, with intermittent success. I used it awhile back and I had the date bit working, but now it is not! I'm convinced it's to do with formatting, but I'm unsure. I've tried various solutions I found on the internet to do with DATEVALUE and TEXT to amend the value for query, but I am struggling and hoping a fresh pair of eyes can see the issue? The issue is at end of formula with column D.

=IFERROR(query(testimport!A:R,"Select *" & IF((left(M6,1))="*","Where C starts with '"& RIGHT(M6,LEN(M6)-1) &"' and A contains '"&$A6&"' and B contains '"&$B6&"' and C contains '"&$C6&"' and E contains '"&$E6&"' and F contains '"&$F6&"' and G contains '"&$G6&"' and L contains '"&$L6&"' and N contains '"&$N6&"' and O contains '"&$O6&"' and P contains '"&$P6&"' and Q contains '"&$Q6&"' and R contains '"&$R6&"'","Where A contains '"&$A6&"' and B contains '"&$B6&"' and C contains '"&$C6&"' and E contains '"&$E6&"' and F contains '"&$F6&"' and G contains '"&$G6&"' and L contains '"&$L6&"' and N contains '"&$N6&"' and O contains '"&$O6&"' and P contains '"&$P6&"' and Q contains '"&$Q6&"' and R contains '"&$R6&"' and D >= '"&$S1&"' and D <= '"&$T1&"'"),1),)

Google sheet link: https://docs.google.com/spreadsheets/d/19myFaD1OlYKDjRJkV_OQFFcN8nfkYbur7mHioUk_Wco/edit?usp=sharing

I've tried the methods in below links..

https://infoinspired.com/google-docs/spreadsheet/how-to-use-date-criteria-in-query-function-in-google-sheets/

https://www.benlcollins.com/spreadsheets/query-dates/

1

I think you're right that this is a date format issue.

On a quick look, when you test 27/07/2019 > 01/08/2019 the result is TRUE
However when 01/08/2019 > 5/08/2019 is tested you have the expected FALSE
When =DATEDIF(L8,L9,"d") is tested or Date A - Date B there's the expected 5 result.

So when I input the data myself (27/07/2019 > 01/08/2019) there's a false. That is the good behavior.

You just need to format your dates on column D. Take D column and use a different date format with the More Formats option.

Here's the mess I made to find out.

Another way to format this column:
Try this formula =DATE(YEAR(D1),month(D1),day(D1)) where D1 is obviously the date with a weird format.

  • Hi PaulJ, thanks for looking into this. The query isn't working on your copy and is display 27/07/2019 when 01/08/2019 is in the input cell...is this intended? – Ceebee86 Aug 2 at 11:01
  • 1
    Thanks, I've taken onboard all your experiments and relooked at some solutions and adding Col4 >= date '"&TEXT(S1,"yyyy-mm-dd")& does the trick! this date formatting is maddening lol! – Ceebee86 Aug 2 at 11:48
  • I must confess i was out of ideas, i'm glad you resolved it. I still didn't quite understood what is the issue here with the formatting, it doesn't make sens. Have a great day. – pjmg Aug 2 at 11:57
  • Well, turns out weirdly that Query language only accepts date in one format which is YYYY-MM-DD in text format, but you need to specify the date clause in the query syntax. – Ceebee86 Aug 2 at 12:20
  • This explain the "date" string in the Query. Glad to learn that today because it could have been a serious issues in the futur when messing with date. I found this material for further explanation and use-cases benlcollins.com/spreadsheets/query-dates Have a great day. – pjmg Aug 2 at 12:29

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.