Search code examples
google-sheetsgoogle-sheets-query

Comparing dates in Google Sheets QUERY() formula

Looking for some assistance please.

To start, here's the function that I'm having trouble with: 

=IFERROR(
  QUERY(
    OrderDetails!A8:Q9,
    "SELECT SUM(J) where Q >= date '" & TEXT(D3,"yyyy-mm-dd") & "' label sum(J) ''"
  ),
  0
)

The dates in the data range (OrderDetails!A8:Q9, columns P is Date & Q is DateOnly) look like this:

data range

I added Q manually in an attempt to make the date-matching work, but P is the raw data which I would prefer to use.

Next the SUM(J) which are just order balances. If I remove the WHERE clause the query runs as expected.

D3 is the column date I want to match to, in the format: 8/13/2018, however I've formatted it on screen to be only DDD.

D3 Date

To show the actual value rather than the header in the cell, I've used label sum(J) ' '.

When running I get the message "Nothing to return".

Can anybody spot an obvious error with the code or my approach? Happy to add any further detail if needed.

Solution

  • Populate Q8 with:

    =left(P8,10)*1

    and copy down.

    The QUERY is failing for attempting to compare a date (in D2/3) with the output of a string function (LEFT). *1 coerces the strings into dates. Left alignment was a clue that the contents were Text.