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..
Best Answer
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 isTRUE
However when
01/08/2019 > 5/08/2019
is tested you have the expectedFALSE
When
=DATEDIF(L8,L9,"d")
is tested orDate A - Date B
there's the expected5
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
. TakeD
column and use a different date format with theMore 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))
whereD1
is obviously the date with a weird format.