I have been working on creating a personal finance spreadsheet and from my experience of using
QUERY previously to create dashboards, I thought I would have no issues with this but I was wrong! The QUERY function itself works and does not show any error but the results are not correct. My formula:
=QUERY(Expense_Breakdown!$A:$E, "select A, C, D WHERE A IS NOT NULL AND A >= date '"&TEXT(DATEVALUE(Overview!$C$2),"yyyy-mm-dd")&"' AND A >= date '"&TEXT(DATEVALUE(Overview!$C$3),"yyyy-mm-dd")&"'",1)
I have a main spreadsheet called "Expense Breakdown" from where I want to display the Date(A), Amount(C) and Category(D) columns when the date range in cells C2 and C3 from my dashboard spreadsheet "Overview" is selected. For example, the expense breakdown spreadsheet has data ranging from 2021-01-01 to 2022-01-01. When I select date range 2021-01-01 to 2021-03-31 (3 months), it doesn't display all the data in that date range and on top of that it also shows data from outside the date range...it just looks a bit messy with data from different dates and thats not what I need! (Note that I have also tried having this date selection within the same spreadsheet and it still gives data from outside the date range selected).
I would really appreciate some insight on this and any solutions! I have tried everything like reformatting the dates in the columns to match yyyy-mm-dd format multiple times as I thought maybe there was a glitch somewhere but its still not working! This is really frustrating and I feel like there is something I am missing and not seeing! Happy to share my spreadsheet if that helps anyone to investigate the problem.