google sheets - How to use date with LIKE inside query? - Stack Overflow

I've got a google form(which feeds to Google sheets) for users to enter information. One of the fields is a datepicker which has this format mm/dd/yyyy.

I have a workbook with several sheets. One sheet (tab) captures the user's input from the google docs form. It's called Form Responses 1. (for example)

On another tab, I'm trying to extract data based on month that comes from column G.

For example, in the first cell, I've tried formulas like this (trying to select all December answer from 12/1/2025 to 12/31/2025):

=query('Form Responses 1' !A2:N, "Select * Where G like '12')

=query('Form Responses 1' !A2:N, "Select * Where G >= '12/1/2025' and <= '12/31/2025')

Anyone know how to get either a LIKE '12%' to work on the datepicker field answer (I know it's not a string) or some other way not using a LIKE that will return every answer with 12 in the month, or mm position?

I've got a google form(which feeds to Google sheets) for users to enter information. One of the fields is a datepicker which has this format mm/dd/yyyy.

I have a workbook with several sheets. One sheet (tab) captures the user's input from the google docs form. It's called Form Responses 1. (for example)

On another tab, I'm trying to extract data based on month that comes from column G.

For example, in the first cell, I've tried formulas like this (trying to select all December answer from 12/1/2025 to 12/31/2025):

=query('Form Responses 1' !A2:N, "Select * Where G like '12')

=query('Form Responses 1' !A2:N, "Select * Where G >= '12/1/2025' and <= '12/31/2025')

Anyone know how to get either a LIKE '12%' to work on the datepicker field answer (I know it's not a string) or some other way not using a LIKE that will return every answer with 12 in the month, or mm position?

• google-sheets
• google-sheets-formula
• google-forms
• google-query-language

Share Improve this question Follow edited Nov 16, 2024 at 17:22 TheMaster 51.4k77 gold badges7373 silver badges100100 bronze badges asked Nov 16, 2024 at 4:01 We5inelgrWe5inelgr 4344 bronze badges 2

• 1 LIKE used for partially matching or wildcard matching. In case of date you need similar BETWEEN in google-sheet. So use > and < greater than and less than operator. – Harun24hr Commented Nov 16, 2024 at 4:43
• 3 It's impossible to have a "google docs excel form". Google Docs is owned by Google, and Excel is owned by Microsoft. You either have a Google Sheets form (which means Excel isn't involved), or you have an Excel form (in which case Google Sheets is not involved). Please edit either your question title and/or the tags to reflect what you're actually using and asking about. – Ken White Commented Nov 16, 2024 at 5:13

Add a comment  | 

4 Answers 4

Sorted by: Reset to default 3

Let assume your date picker cell is B2 then use following QUERY() formula-

=QUERY('Form Responses 1' !A2:N,"select * where G>= date '" &TEXT(B2,"yyyy-mm-dd")& "' and G<= date '" & TEXT(EOMONTH(B2,0),"yyyy-mm-dd")& "'")

If you want to put dates manually then try-

=QUERY('Form Responses 1' !A2:N,"select * where G>= date '" &TEXT(DATE(2025,12,1),"yyyy-mm-dd")& "' and G<= date '" & TEXT(DATE(2025,12,31),"yyyy-mm-dd")& "'")

It's important to note that query uses yyyy-MM-dd format for date internally, regardless of what format you use on the outside(as long as it is recognized as a date format by Google sheets). So, like would work like this:

Any year's December:

"where G like '%-12-%' "

2024th December:

"where G like '2024-12-%' "

2024th December 10th-19th day:

"where G like '2024-12-1_' "

If G is datetime(not just date), we need an extra wildcard at the end

"where G like '2024-12-1_%' "

2020-2029, Any month, 20th-29th:

"where G like '202_-%-2_' "

If G is datetime(not just date), we need an extra wildcard at the end

"where G like '202_-%-2_%' "

Any date with number 12 anywhere(year 2012 or 12th of 2025 or December):

"where G like '%12%' "

Do note that the scalar functions inside query like month offer different control.

Any year's December(11th month as months start from 0(not 1)):

"where month(G) = 11"

...or:

"where month(G)+1 = 12"

(trying to select all December answer from 12/1/2025 to 12/31/2025)

=query('Form Responses 1' !A2:N, "Select * Where G like '12')

To do that with hard-coded parameters, use year() and month(), like this:

=query('Form Responses 1' !A2:N, "where year(G) = 2025 and month(G)+1 = 12", 0)

To get rows that are in the same month as the date in cell D1, use this:

=query( 
  'Form Responses 1' !A2:N, 
  "where year(G) = " & year(D1) & 
  "and month(G)+1 = " & month(D1), 
  0 
)

See query(), year() and month().

The easiest way is to use Filter, month, and year:

=filter('Form Responses 1' !A2:N,month('Form Responses 1' !G2:G)=12,year('Form Responses 1' !G2:G)=2025)