I have been getting on the GAS and am thinking that QUERY might be a part of my solution. I am therefore trying to get everything working in Sheets first. I have followed your guide to QUERY, even adding in two dynamic selectors (is that what they are called?) that I got from your work on VLOOKUPS. My question is filtering by dates. I have followed your instructions for filtering between two dates:
=QUERY(Data!$A$1:$H$136,”select C, B where B > date ‘”&TEXT(A1,”yyyy-mm-dd”)&”‘ and B <= date ‘”&TEXT(B1,”yyyy-mm-dd”)&”‘”,1)
But fear that I maybe limited as I have recorded my dates using DD-MM-YYYY. Sheets recognises this as a date as a formatted the cells as ‘DATES’. My spreadsheet settings have also been changed to ‘Australia’. I am wondering if you have any thoughts or suggestions on this? Here is a link to my sheet.
I never cease to be surprised with what I can do with the QUERY formula in Google Sheets. I had a problem today where I wanted to match two datasets using the date column. The problem was that they were in two different formats. So I started searching. Low and behond, I discovered that you can in fact format using the QUERY format. The formula looks something like this:
=QUERY(A1:C7,"select * format B 'd-mmm-yy (ddd)'")These are the available values:
d | Day without a leading zero for 1-9 (i.e. 7)
dd | Day with a leading zero for 1-9 (i.e. 07
ddd | Day as an abbreviation (i.e. Wed)
dddd | Day as a full name (i.e. Wednesday)
m (if not preceded or followed by hours or seconds) | Month without a leading zero (i.e. 8)
mm (if not preceded or followed by hours or seconds) | Month with a leading zero (i.e. 08)
mmm | Month as an abbreviation (i.e. Aug)
mmmm | Month as a full name (i.e. August)
mmmmm | First letter of the month (i.e. A)
y or yy | Two digit year (i.e. 19)
yyy or yyyy | Full numeric year (i.e. 2019)
I know Ben Collins has spoken about formatting dates before, but I never knew there were all these options.