Google Sheets Query – Date Formatting Guide

google sheetsgoogle-sheets-query

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/

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 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.

Related Topic