I frequently utilize the SQL-like Query function in Google Sheets to condense data across various spreadsheets, and it has functioned effectively for an extended period, though not swiftly. Recently, I’ve encountered difficulties with certain queries, particularly those that involve comparing dates in the source dataset with the function TODAY(). I have encountered a problem that I’ve illustrated in a shared Google spreadsheet using fabricated data.
The dataset is located in the range A1:D6, containing the following columns: “Serial No.”, “Type”, “Location”, and “Warranty Expiration”, with the final column formatted as a date. My formula placed in A9 consolidates the data as follows:
=QUERY(A1:D6, "SELECT B, COUNT(A) GROUP BY B PIVOT C")
The outcome is as expected. However, when I incorporate a filter using WHERE with a date condition, the query seems to fail. I intend to create a summary table similar to the previous one but include only entries where column D has past dates. My intended formula is:
=QUERY(A1:D6, "SELECT B, COUNT(A) WHERE D < NOW() GROUP BY B PIVOT C")
If I adjust the filter to exclude dates, the result is correct. What steps should I take to achieve the desired summary?
It sounds like this could be an issue with how date comparison works in Google Sheets. The NOW() function returns not just the date but also the current time, which may affect the comparison if your Warranty Expiration dates don’t include time. To compare only the dates correctly, you might want to use the TO_DATE function. Try modifying your formula slightly like this: =QUERY(A1:D6, "SELECT B, COUNT(A) WHERE D < DATE '" & TEXT(NOW(), "yyyy-MM-dd") & "' GROUP BY B PIVOT C"). This approach will ensure that you’re comparing just the date portion of NOW() with the Warranty Expiration dates.
I have faced similar issues before, and one thing that worked for me was ensuring the date format in column D matches the one generated by the TODAY() function. Google Sheets may interpret dates differently depending on their format settings. Converting dates in the column explicitly to the same format might resolve the inconsistency. You can use =TEXT(D2, "yyyy-mm-dd") to check if the dates in column D are aligned with the format of TODAY(). If they mismatch, use DATEVALUE to ensure they reconcile perfectly.
Hey there! It might be a formatting issue with how dates are stored and compared in the cells. Make sure column D’s cells are truly in ‘date’ format. Plus, use DATE() instead of NOW() for a cleaner cut-off on the day level. Try it, hope it helps! 