How to compare dates or date against today with query on google sheets?

by

There’s no today() in Query. Use now() instead:

=query(sheet1!A3:N, " select I,M where I = 'Singapore' AND M > now() ",0)

Or if you want now() without time(equivalent to TODAY()), use:

todate(now())

For this to work, provided you have all the correct dates in M in any format, which Google sheets recognises (i.e., the formula bar shows the correct date or time) regardless of the actual string. If not, You should manually convert all those remaining dates to correct format. The correct format of date to be entered in the query formula is date 'yyyy-mm-dd'. It doesn’t matter what format the date is in sheets ( as long as Google sheets recognises this), but the actual formula must only contain date in this format for comparison. For example , to find all dates less than 31,August, 2017,

=query(A2:B6, "select A where A < date '2017-08-31'")

You can use this to figure out all the dates, which Google doesn’t recognise: N1:

M:M*1

If you get an error in the helper column N, then those dates are not recognised. Even if you did not get error, it is possible that Google sheets mis-recognizes the date. There is also a more specific function:

=ARRAYFORMULA(ISDATE(M:M))

References:

More Related Contents:

  1. Difference between two dates expressed as years, months, days (in one column)
  2. ArrayFormula of Average on Infinite Truly Dynamic Range in Google Sheets
  3. Google Apps Script date format issue (Utilities.formatDate)
  4. Query is ignoring string (non numeric) value
  5. Col BY causing parse error in Google sheets query
  6. `uuuu` versus `yyyy` in `DateTimeFormatter` formatting pattern codes in Java?
  7. How to convert milliseconds into human readable form?
  8. No permission to call msgBox in Google Apps Scripting
  9. Get the last non-empty cell in a column in Google Sheets
  10. Periodically refresh IMPORTXML() spreadsheet function
  11. Is there a custom function or script that returns gid of a specific sheet in Google Sheets?
  12. Google spreadsheet “=QUERY” join() equivalent function?
  13. Selecting the last value of a column
  14. How to convert ISO Date to UTC date in Hive
  15. ArrayFormula and “AND” Formula in Google Sheets
  16. VBScript How can I Format Date?
  17. Using built-in spreadsheet functions in a script
  18. Determine season given timestamp in Python using datetime
  19. IMPORTXML and right XPath from Bloomberg price [duplicate]
  20. What factors determine the memory used in lambda functions?
  21. Changing date format to “%d/%m/%Y”
  22. How to list all permutations without repetition?
  23. How to create all possible pair combinations without duplicates in Google Sheets?
  24. Compare two dates Google apps script
  25. How do I align date and values to gridlines in Google Chart?
  26. Same date in different time zone
  27. Can the google spreadsheet ‘query’ function be used in google apps script?
  28. In ARRAY_LITERAL, an Array Literal was missing values for one or more rows – indirect formula error in google spreadsheet
  29. How to create INDIRECT array string of multiple sheet references in Google Sheets?
  30. Can the google spreadsheet ‘query’ function be used in google apps script?

Leave a Comment