How to Use Date Criteria in Query Function in Google Sheets

Using dates as the criteria are a complicated part of any criteria formation in the Google Sheet functions. In most cases, you can not use it in its original format. The date criteria in Google Sheets Query function are no exception to this.

It should be converted to text using different approaches, and the usage may vary from one function to another.

I will explain how to use date criteria in the Query function in Google Sheets below.

By saying date criteria, I mean using dates in the Query ‘where’ clause.

Things to Know Before We Talk About Date Criteria [Date in ‘Where’ Clause] in Query Function

When we want to filter a column using a date as a condition (criterion), we must treat it as a string literal in Google Sheets Query.

So the format of using a text in the Query ‘Where’ clause applies to date also.

Text Criterion Hardcoded (directly entered inside the formula):

"select A,B where F='Sold' "

Text Criterion as a Cell Reference:

"select A,B where F='"&E2&"' "

When we come to date, we must additionally use a keyword before the criterion and format it to text. We will learn that below.

Steps: How to Use Date Criteria in Query Function in Google Sheets

When we use a date condition in the above second formula, the date in cell E2 must be either in text string format or we should convert that within the ‘Where’ clause as a text string.

Let’s see how the above two formulas change when the condition is not a text string but a date.

1. When inside formula (hardcoded).

The keyword ‘date’ is used as an identifier to tell the Query that the text is a date.

"select A,B where F=date '2010-08-30' "

2. When cell reference (cell contains a date).

"select A,B where F=date '"&TEXT(E2,"yyyy-mm-dd")&"' "

3. When cell reference (cell contains a text formatted date).

In the below case, the date in E2 must already be in text string format.

"select A, B where F = date '"&E2&"' "

Note: You should follow “yyyy-mm-dd” format in Query formula.

There are two methods to convert the date to a text string in Google Sheets to use in Functions. One is compact and the other is the long-winded approach.

I’ve used the compact method of date-to-text conversion in the formula under point no. 2 above.

You can convert the date to a text string within a cell and use that cell reference in the formula. The above point no. 3 is an example.

Example to Date Criteria in Query Function [Use of Date in Query Function ‘Where’ Clause]

To know how to use date criteria in the Query function in Google Sheets, follow the below examples.

Sample Data (Must be entered in cell ranges A1:F16).

As a side note, if you want to know how I created the above interactive table, please follow this tutorial.

Now see some of the formulas below where the date is the criteria.

One more thing. The name sourcemaster in the below formula is the named range of data. You can instead use sheet reference directly as Sheet1!A1:F16 in the Query.

Query Formula 1

In this Query formula, I’ve used a date condition directly in the formula as a text.

=query(sourcemaster,"select A,B,C,D,E,F where F = date '2010-08-30' ")

Query Formula 2

Here I used the date criteria in the Query function to select rows that fall between two given dates in a column. Here also the date directly used.

=query(sourcemaster,"select A,B,C,D,E,F where F > date '1990-1-1' and F < date '2000-12-13' ")

Query Formula 3

Here the criteria in cell H2 is a cell reference, and that cell contains a date to use as the criterion.

=query(sourcemaster,"select A,B,C,D,E,F where F = date '"&TEXT(H2,"yyyy-mm-dd")&"'")

The alternative option is to convert the date in cell H2 as a text string in another cell, for example, in cell H3. Then use that cell reference as below.

=query(sourcemaster,"select A,B,C,D,E,F where F = date '"&H3&"'")

You can follow the long-winded or compact method of date conversion. I’ve used the following formula in cell H3.

H3 Formula: =text(H2,"yyyy-mm-dd")

Date Criteria in Query Function in Google Sheets - Formula Results

You can find all the above three formula results in the screenshot below in their order from top to bottom.

I hope that you could be able to understand the usage. Any doubt, please drop in comments.

102 COMMENTS

Spencer Barfuss September 5, 2022 At 6:20 am

Hi, Prashanth,

So, I’m trying to QUERY data where a column’s time is less than a certain time specified in a cell.

I get an “N/A” that says, “Query completed with an empty output.”

I can’t seem to figure this out, no matter how many videos I watch on the subject.

Here’s the URL of my Sheet: —removed by admin—

Formula is in cell A2 in the Early tab.

Thanks for your help!

sameer kumar July 13, 2022 At 6:40 pm

Hi Prashanth,

I want to know to put query for criteria is “TODAY”.

- Prashanth July 14, 2022 At 9:16 am
  
  Hi, Sameer Kumar,
  
  Here are two examples to help you learn the use of today in Query.
  
  Popular One:
  
  =query(A1:B,"select * where A = date '"&TEXT(today(),"yyyy-mm-dd")&"'")
  
  Suggested:
  
  =query(A1:B,"select * where A = todate(now())")
  
Khalfani July 2, 2022 At 9:26 pm

Hello,

I’m trying to add numbers in a column using a one week date period as the selection criteria.

It would select about 10 rows out of 30+ rows that have a date match, then add the numbers in column K from all the selected rows and display the sum in a single cell.

Thoughts?

- Prashanth July 3, 2022 At 12:34 pm
  
  Hi, Khalfani,
  
  That seems possible.
  
  To sum column K when column A contains dates.
  
  =Query(A1:K,"Select sum(K) where A >= date '"&TEXT(L1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(M1,"yyyy-mm-dd")&"' label sum(K)''")
  
  Enter the start date in L1 and the end date in M1.
  
  You can enter this formula in L2.
  
Liz May 28, 2022 At 1:09 am

Hello, I’m looking to show a range between two dates and getting stuck.

Currently, this works great with dates as static text. But can’t take dates from a set of cells.

This works:

=query(ServicesPricings,"select O,P where B > date '2022-5-23' and B = date '"&TEXT(C12,"yyyy-mm-dd")&"' and B <= date '"&TEXT(D12,"yyyy-mm-dd")&"')")

Can you help me with my date formatting to get through this?

- Prashanth May 29, 2022 At 11:03 am
  
  Hi, Liz,
  
  You may try this.
  
  =query(ServicesPricings,"select O,P where B > date '2022-5-23' and B <= date '"&TEXT(D12,"yyyy-mm-dd")&"' or B = date '"&TEXT(C12,"yyyy-mm-dd")&"'")
  
  If that is working, please read about Defining Explicit Precedence in Google Sheets Query (Logical Operators).
  
  If not, share a copy of your sheet (URL) below in your reply.
  
Sinchai April 14, 2022 At 5:56 pm

Hello, Could you help:

On “Daily Report,” in cell A4, what did I do wrong to return empty output?

— URL (sample sheet) removed by admin —

- Prashanth April 14, 2022 At 6:57 pm
  
  Hi, Sinchai,
  
  You are applying the date criteria in a text column.
  
  Column 25 seems formatted to text in your source data. You have a date column, i.e., column 4.
  
  So replace WHERE Col25 = date '"&TEXT(D1,"yyyy-mm-dd")&"' with WHERE Col4 = date '"&TEXT(D1,"yyyy-mm-dd")&"'.
  
  - Sinchai April 15, 2022 At 8:15 am
    
    Dear Prashanth,
    
    That works. Thank you so much for your help.
    
Tim Brill March 19, 2022 At 6:44 pm

Thanks for the awesome article! I have learned so much from you.

I did want to check to see if this was possible or if you knew of a way I could do this.

I want to pull revenue and conversion rate from separate google sheets by the date and have the sum of revenue and the average conversion rate onto the master spreadsheet, filtered by the date. Is that possible?

Please let me know if you need any more information.

Thank you!!

- Prashanth March 20, 2022 At 10:36 am
  
  Hi, Tim Brill,
  
  I can’t comment on it until I see your data.
  
  Can you share a sample sheet below via ‘Reply’?
  
  Include the data to import in sheet1 and sheet2 and use sheet3 as the master sheet.
  
  N.B.: I won’t publish the URL.
  
Markdg January 20, 2022 At 2:05 am

Hi, I have a similar sheet that is being updated regularly, and I want only to pull the last update for each customer.

Order Number | Name | Item | QTY | Updated

1001 | Bill | ABC | 10 | 30/07/2021
1001 | Bill | DEF | 5 | 30/07/2021
1001 | Bill | GHI | 5 | 30/07/2021

1001 | Bill | ABC | 10 | 07/08/2021
1001 | Bill | DEF | 5 | 07/08/2021
1001 | Bill | GHI | 7 | 07/08/2021

1001 | Bill | ABC | 2 | 09/08/2021
1001 | Bill | DEF | 4 | 09/08/2021
1001 | Bill | GHI | 2 | 09/08/2021

I want to pull a query back with this group by order number for the last update and sum the QTY.

1001 | Bill | 8 | 09/08/2021

=query(Orders!A1:E,"Select A, B, Sum(D), E group by E Where E = date '"&Text(Max(Orders!E:E),"YYY-MM-DD")&"'",1)

I am getting an error. Any idea? Thanks

- Prashanth January 20, 2022 At 12:38 pm
  
  Hi, Markdg,
  
  Please follow the correct Query clause order.
  
  The “Group by” clause should come only after the “Where” clause. Also, there are other issues.
  
  Please try the below formula.
  
  =ArrayFormula(query(A1:E,"select A,B,Sum(D),max(E) where E=date '"&TEXT(max(E3:E),"yyyy-mm-dd")&"' group by A,B"))
  
Net October 21, 2021 At 10:00 pm

Hi,

I would like to make a Query that I could choose from the drop-down list, and it gives me the input from my analytics tab.

But I am having trouble doing so with the date function within query or issues with the year data validation cell.

- Prashanth October 24, 2021 At 9:43 am
  
  Hi, Net,
  
  I wish to help you out but require a sample sheet.
  
  You can feel to create and share one via “Reply” below, which won’t be published.
  
Nakita October 15, 2021 At 8:14 pm

Hi there, I am struggling with an import query that is throwing a formula parse error.

The objective is to count the number of entries that are not null in Column M (col13) based on the date ranges in column E (col5).

Thank you in advance! Here is the formula:

=QUERY(IMPORTRANGE("URL", "Gifts!E2:M"), "SELECT COUNT(col13) WHERE Col5 >= date '2019-01-01' AND Col5 < date '2019-02-01' AND Col13 IS NOT NULL ")

- Prashanth October 17, 2021 At 1:31 pm
  
  Hi, Nakita,
  
  There are only Nine columns in your imported data starting from E to M.
  
  So, modify the formula accordingly.
  
  - Nakita October 18, 2021 At 10:19 am
    
    Hey Prashanth,
    
    Thanks for getting back to me! I went ahead and edited the columns. (of course, I always get mixed up with the numbering and recall systems, thank you for that light bulb.) However, I am still getting a parse error. I checked the import range, and it appears that all is correct there. Any thoughts?
    
    - Prashanth October 19, 2021 At 11:31 am
      
      Hi, Nakita,
      
      There are still errors.
      
      It should be COUNT(Col9), not COUNT(col9).
      
Microchipmatt September 23, 2021 At 6:48 am

Hi Prashanth. I need help with a query. You seem brilliant at it.

I have a Sheet that has six fields.

The two most important fields are Cell D and F with Dates, in these fields.

I want to query all the data, but where Cell D and Cell F are BOTH less than the date 08/31/2021.

Here is what I have so Far:

=query(Sheet1!$A$1:$F$424,"select A, B, C, D, E, F where D < date '"&TEXT(DATEVALUE("08/30/2021"),"yyyy-mm-dd")&"'",1)

I can seem to get the rest, so that its looking if BOTH cells are less than date 08/31/2021. Thanks for your help in advance.

- Prashanth September 23, 2021 At 8:58 am
  
  You can try logical AND in Query.
  
  E.g.:-
  
  =query(Sheet1!$A$1:$F$424,"select A, B, C, D, E, F where D < date '2021-8-31' and F < date '2021-8-31'")
  
  An outer Query can remove blanks in both date columns.
  
  =query(query(Sheet1!$A$1:$F$424,"select A, B, C, D, E, F where D < date '2021-8-31' and F < date '2021-8-31'"),"Select * where Col4 is not null and Col6 is not null")
  
Brent September 18, 2021 At 12:54 am

Hello! Your article is quite useful and informative.

I am creating a search screen against a number of the columns. For the simple text columns, the query correctly ignores the search fields that are not populated.

However, when I try to do this with a DATE field, I get the error “Query completed with an empty output.”

Here’s my query – hopefully, you can provide suggestions on how to tweak this to work:

=query(Infractions,"select A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P where lower(A) contains '"&lower(A7)&"' and lower(B) contains '"&lower(B7)&"' and lower(D) contains '"&lower(D7)&"' and G contains date '"&TEXT(G7,"yyyy-mm-dd")&"' and lower(H) contains '"&lower(H7)&"'")

- Prashanth September 18, 2021 At 11:10 am
  
  Hi, Brent,
  
  Your Query formula works on my end though you can replace G contains date with G = date.
  
  If you want further assistance, you can share a sample of your sheet below (I won’t publish it).
  
  - Brent September 20, 2021 At 11:36 pm
    
    Thanks, Prashanth. See sheet link below.
    
    — The Sheet address (URL) removed by admin —
    
    - Prashanth September 21, 2021 At 9:36 am
      
      Hi, Brent,
      
      Replaced the Query part;
      
      and G contains date '"&TEXT(G3,"yyyy-mm-dd")&"'
      
      with;
      
      and "&if(isdate(G3),"G=","G>="&"date '"&TEXT(G3,"yyyy-mm-dd")&"'
      
      Please check it in your Sheet.
      
Sandeep Gaur August 24, 2021 At 3:50 pm

I have a google sheet (actually, it is google form responses in google sheet) in which two fields are there Timestamp and Name of employees.
I wish to count the number of employees present, on a particular date, on another spreadsheet (workbook).
Please suggest how to use the query formula?

- Prashanth August 25, 2021 At 11:33 am
  
  Hi, Sandeep Gaur,
  
  For that, you require to use Query with Importrange.
  
  E.g.:
  
  =query(IMPORTRANGE("URL","'Form Responses'!A1:B"),"Select count(Col2) where Col1 >= date '2021-8-25' and Col1 < date '2021-8-26' label count(Col2)''")
  
  Replace URL with the link copied from the address bar of the source sheet and Form Responses with the actual tab name in the workbook.
  
Judith Goh April 26, 2021 At 2:35 pm

Hi Prasanth,

Please suggest how I can amend the function below. I need to add one more condition where the new tab picks the dates which are “today” only. The date is in Col 1.

=QUERY(IMPORTRANGE(“URL”, “PAST!A19273:J”),”Select Col1,Col2,Col3,Col5,Col6,Col8,Col9 where Col6>1"

Thank you in advance for your reply.

- Prashanth April 27, 2021 At 9:29 am
  
  Hi, Judith Goh,
  
  You may try this Query that contains TODAY() criteria.
  
  =QUERY(IMPORTRANGE("URL", "PAST!A1:J10"),"Select Col1,Col2,Col3,Col5,Col6,Col8,Col9 where Col6>1 and Col1=date '"&TEXT(today(),"yyyy-mm-dd")&"'")
  
  - Judith Goh May 12, 2021 At 7:43 am
    
    It works! Thanks a lot!
    
António Laureano March 7, 2021 At 6:51 am

Hi. I need some help with a formula. I need to select a range between two dates. This range must cover the file number and the description. The sheet is the following:

— link removed by admin —

For the dates, I have used the following formula:

=query(A3:C8,"Select * where A>= DATE'"&TEXT(F2,"yyyy-mm-dd")&"' AND A<=DATE'"&TEXT(G2, "yyyy-mm-dd")&"'")

Also in the formula, I need to select the file number and the description.

Thanks in advance.

- Prashanth March 8, 2021 At 1:21 pm
  
  Hi, António Laureano,
  
  You can use this.
  
  =query(A3:C8,"Select * where A>= DATE'"&TEXT(F2,"yyyy-mm-dd")&"' AND A<=DATE'"&TEXT(G2, "yyyy-mm-dd")&"' and lower(B)='ls200' and lower(C) contains 'call'")
  
  I have used the LOWER() function to neutralize the case sensitivity of the Query. Instead of lower(C)='call' I have used the CONTAINS clause since you have "call", "phone call" etc. under description.
  
BR March 3, 2021 At 12:58 pm

Hi there,

I am having issues where the query function is returning a blank result.

- Prashanth March 6, 2021 At 10:03 am
  
  Hi, BR,
  
  I have checked your sheet. It’s viewable only. Further, you have disabled the option to copy the sheet. So there is no way to check the formula 🙁
  
  If you share the sheet again after changing the sharing settings, please do include your expected result in the sheet also.
  
Ian February 24, 2021 At 10:53 pm

Hi!

I would like to ask if it is possible to make a query where column A will be the “date” when the entry was made and details will be from column B to M.

I want to query the data by using the date it was entered “A” which will show the information of those filtered data only.

- Prashanth February 26, 2021 At 7:52 am
  
  Hi, Ian,
  
  You can try this Query in cell N1.
  
  =query(A1:M,"select * where A = date '2021-2-26'",1)
  
  Change the date criterion used in the Query.
  
  If you still have a problem, you can consider sharing your sheet link via “Reply”.
  
Lakmal December 25, 2020 At 3:11 pm

Can’t seem to get the sum of values within a specific date range and the tag but it works when only one date and a tag is selected.

I need to be able to select the from and to dates (both days included) and get the total for say views, etc. But somehow it returns only a 0. I tried just querying but still doesn’t work with the range. What am I doing wrong? Appreciate your kind help.

- Prashanth December 27, 2020 At 6:25 am
  
  Hi, Lakmal,
  
  The Query function is very ‘sensitive’ when it comes to ‘mixed’ type data. Your date column contains text strings (the string ‘DAILY STATS’) too.
  So instead of Query use the Filter function.
  
  The below filter formula would work.
  
  =Filter(Datasheet!$G$1:$G$2000, Datasheet!$F$1:$F$2000="Daily", Datasheet!$C$1:$C$2000>=B2, Datasheet!$C$1:$C$2000<=C2)
  
Josh Rodriguez December 19, 2020 At 5:26 am

Hi,

Thanks so much for this article. I notice in the examples shared, everyone has the date in a single column however I have my dates spread across a single row. For example:

A | B | C | D
1/1/2020 | 2/1/2020 3/1/2020 4/1/2020
10 things | 15 things | 13 things | 5 things

I hope the formatting comes through when I hit save but effectively my table is organized so I can see how many things have been sold (for this example) given a certain snapshot date.

As these dates will continue into the future I would like to create a query that will only display the last 12 months from Today’s worth of data so that I can have a graph that dynamically updates.

Is there a way to query the date against a row instead of a column?

- Prashanth December 19, 2020 At 7:21 am
  
  Hi, Josh Rodriguez,
  
  I think I can write a formula for you. Can you share some mockup data so that I can write a tutorial based on it?
  I am confused with the text in row # 2.
  
- Prashanth December 20, 2020 At 5:41 pm
  
  Hi, Josh Rodriguez,
  
  Try this.
  
  How to Filter Current Week Horizontally in Google Sheets.
  
Rahul November 17, 2020 At 1:16 pm

Hello,

I am trying to build a query to compare "R" & "now()" to see if the difference is 5.

However, I keep on getting the error

“Unable to parse query string for Function QUERY parameter 2: Can’t perform the function difference on values that are not numbers”

Query used :

=QUERY(RAW!A2:W1000,"select A where (C contains 'Completed' and now()-R>5 ) ORDER BY C",-1)

What to do?

- Prashanth November 19, 2020 At 7:56 am
  
  Hi, Rahul,
  
  What is “R” in your formula? Are you referring to the column R? If so, what’s that value in that column (date or timestamp)?
  
  Please clarify.
  
Guenahel September 9, 2020 At 11:06 am

I am trying to use date criterion on my spreadsheet but I don’t get the result that I wanted.

Here’s the formula that I used.

=query('Follow-up'!$A$2:$F, "Select A, B, C, D, F WHERE F = "&D1&"")

D1 – is the date
F – Where the dates I am filtering

I also used this one;

=query('Follow-up'!$A$2:$F, "Select A, B, C, D, F WHERE F = date '"&TEXT(D1,"yyyy-mm-dd")&"'")

But it says the same thing – Query completed with an empty output.

- Prashanth September 9, 2020 At 1:48 pm
  
  Hi, Guenahel,
  
  In my test, the second Query formula has worked perfectly.
  
  It takes the date criteria from cell D1 and filters the data in A2:F for column F contain the criterion.
  
  If you see #N/A error, there may be one possible reason.
  
  You might have entered the criteria in MM/DD/YYYY format and the dates in column F are in DD/MM/YYYY or vice versa. Please check that.
  
  If you still have a problem, you can try to share the link of your sheet with me. I won’t publish the link (avoid sharing personal info).
  
  - Guenahel September 10, 2020 At 10:57 am
    
    Sure. How do I share mine, please
    
    - Prashanth September 11, 2020 At 11:12 am
      
      Hi, Guenahel,
      
      Open your file in question or create a mockup of that file. Click the “Share” (green color) button on that file. Get the shareable link and share it via comment. That comment won’t be published.
      
Hima Bindu July 1, 2020 At 4:39 pm

Thanks for your time Prashanth. But it’s not working. showing loading….. only
and I tried alternate formula that also not working when I gave the date range. If I remove date range the value fetching.

Don’t know where is the fault 🙁

- Prashanth July 2, 2020 At 11:14 am
  
  I’m totally in the dark. Access to your sheet is required. Consider sharing if it doesn’t contain personal/confidential data.
  
Hima Bindu June 30, 2020 At 7:30 pm

Hi,

I tried using this formula but throwing an error. Can you please look into it. Trying to get the Sum based on selected criteria and date range.

=QUERY(IMPORTRANGE("URLlink","Sheetname!H:H"),"Select Col8, where Col9='"&H4&"' and Col5>=date '"&TEXT($B$1,"yyyy-mm-dd")&"' and Col6<=date '"&TEXT($C$1,"yyyy-mm-dd")&"' group by Col8")

- Prashanth July 1, 2020 At 4:09 pm
  
  Hi, Hima Bindu,
  
  There are 3 main issues in your formula!
  
  1. You have missed the SUM function.
  2. I guess there is a typo in the date criteria column. Instead of Col6 you might want to use Col5.
  3. You are importing only a single column (column H). Then how Query can check the columns 5 and 9 for the criteria in cell H4, B1, and C1?
  
  Here is the working example of your formula.
  
  =QUERY(IMPORTRANGE("URLlink","Sheetname!A1:H"),"Select Col8, sum(Col2) where Col9='"&H4&"' and Col5>=date '"&TEXT($B$1,"yyyy-mm-dd")&"' and Col5<=date '"&TEXT($C$1,"yyyy-mm-dd")&"' group by Col8")
  
  Change the sum column and group by column as per your data.
  
  See if that helps?
  
Rebecca April 3, 2020 At 1:22 pm

Hi,

I have copied the above when I write the query I receive an error value;

“unable to parse ….. NO_Column :a”

- Prashanth April 4, 2020 At 11:17 am
  
  Hi, Rebecca,
  
  Try replacing Select a in your formula with Select A or Select Col1.
  
Muqri October 9, 2019 At 9:23 am

Hi Prashanth,

Scenario:

I want to assign 4 persons to make delivery service based on their availability at that time. So, I will arrange their job equally A > B > C > D and rotate.

But, if one person is unavailable, I can simply use the Attendance Table (tick on Available Table ) to ignore that one person.

For example, person B is unavailable at that time. So, the PIC will rotate to be A > C > D until person B is available to do his job again.

I want to assign the person based on the attendance table at column H (PIC) using formula

But, I don’t want to change all PIC in that sheet ….just change the PIC sequence based on attendance table (availability of the person) during that time and afterward until they available again.

- Prashanth October 9, 2019 At 1:50 pm
  
  Hi, Muqri,
  
  Here is my formula example.
  
  https://docs.google.com/spreadsheets/d/1H-ZrW4ytZSg1ri1vjbF2mjVzVyTweMbKatpQeP_X-4o/copy
  
  I hope that helps?
  
  Best,
  
  - Muqri October 9, 2019 At 2:06 pm
    
    Hi Prashanth,
    
    THANK YOU SO MUCH FOR THE HELP!
    
    Been trying to figure this out for weeks 🙂
    
    I always refer to your website for google sheet problems…not expect your reply and helps.
    
    Thanks again! Hope you have a really wonderful day.
    
    - Ether October 15, 2019 At 7:08 am
      
      Hi Prashanth,
      
      These problems really caught my interest and also the solution is great too!
      
      I have a few questions, is it possible that the “PIC” data is kept even after the day have changed?
      For example, the user can still know who is the PIC for the food ordered for yesterday and day before
      
      One more question, Even though it might be not convenient for an algorithm but is it possible to make a formula for one particular food order that meets the condition (today and available)
      
      Thank you, 🙂
      
      - Prashanth October 15, 2019 At 8:29 am
        
        Hi, Ether,
        
        Q: Is it possible that the “PIC” data is kept even after the day have changed?
        
        A: It’s not possible using a formula.
        
        Q: is it possible to make a formula for one particular food order?
        
        I have added a new formula in cell M1 that only considers the food item “meal set”.
        
        ={"PIC"; ArrayFormula(IFNA(Vlookup(ArrayFormula( if((int(A2:A)=today())*(D2:D="meal set")=1, row(A1:A),)),{ArrayFormula(array_constrain( sort(if((int(A2:A)=today())* (D2:D="meal set")=1,row(A1:A),)), countifs(int(A2:A),today(),D2:D,"meal set") ,1)),ArrayFormula(array_constrain( transpose(split(rept(TEXTJOIN("|", true,filter(I3:I,J3:J=true))&"|", ROUNDUP((countifs(int(A2:A), today(),D2:D,"meal set")-countif( J3:J,true))/countif(J3:J, true))+1),"|")),countifs(int(A2:A), today(),D2:D,"meal set"), 1))},2,0)))}
        
        Best,
        
Muqri October 8, 2019 At 12:27 pm

Hi, I have a question.

Can we divide data based on the available persons (A, B, C, D)?

Means that data being divide equally for the persons (A, B, C, D)

And if that person(B) does not available, data will be divide equally to only available persons (A, C, D).

- Muqri October 8, 2019 At 12:30 pm
  
  Also only divide data for today’s date. Thank in advance!
  
- Prashanth October 8, 2019 At 2:44 pm
  
  Hi, Muqri,
  
  Seems possible. If you share an example (mockup data), I will probably code a formula and write a tutorial based on that.
  
  Expecting your response.
  
  Best,
  
  - Muqri October 9, 2019 At 7:57 am
    
    Hi Prashanth,
    
    Thank you for your reply really appreciate it.
    
    This is the sample for attendance.
    
    …Sheet copied by the admin and removed the link from here…
    
    - Muqri October 9, 2019 At 8:05 am
      
      ” the person sequence change based on attendance table. Based on the time we update the attendance table, it will change the person sequence until we update again the attendance table… ”
      
      ** actually I put a comment in the sheet but I make it as view only.. and I also put time respond that might be helpful 🙂
      
      - Prashanth October 9, 2019 At 8:51 am
        
        Hi, Muqri,
        
        I have copied your Sheet.
        
        I need some clarifications. I didn’t understand ‘divide data’
        
        Do you want to prepare that attendance table using a formula?
        
        or
        
        Do you want to filter the data in the range A1:G based on today’s date?
        
        If not, please enter your expected result on the Sheet and the logic. I’ll follow that.
        
        Best,
        
Kou August 28, 2019 At 9:48 pm

I have a drop-down and would like to create a column that would populate all the dates the instance selected in the drop-down occurred.

For example: If my drop down contained places, I’d like to select grocery store and have the column next to it populate with all of the dates recorded that I went to the grocery store.

I’m not the best at explaining, sorry!

From that list of dates I need to create associated metrics, but I can’t figure out how to populate the list date.

- Prashanth August 29, 2019 At 9:09 am
  
  Hi, Kou,
  
  Please see my example Sheet HERE.
  
  The formula is in cell D2. Check that get back to me for any changes required.
  
  - Kou August 30, 2019 At 11:04 pm
    
    This did the trick!! Thanks so much! I’ll spend time deconstructing this to add it to my excel dictionary. Thank you again!!
    
Kou August 28, 2019 At 1:27 am

Thanks so much for your tutorials and help!

I’m hoping to create a list of dates that are dependent on a selection from a drop-down menu. This list of dates would vary in size depending on the selection in the drop-down menu. Is this possible? How would I go about this?

- Prashanth August 28, 2019 At 9:18 am
  
  Hi, Kou,
  
  Do you want to populate dates based on a date range? I mean two drop-downs; one contains the start date and another the end date.
  
Vidya July 26, 2019 At 11:58 am

Hi Prashanth,

I have an issue with matching values for dates on two different tabs in Google Sheets.

I have hourly values each day and I have to obtain the average value per day and input it in another tab with dates.

I am using this formula below currently and not sure how to go about to apply Query on this. This formula requires me to input the dates manually which is not feasible as my file accounts for one year.

=AVERAGEIF('H2no'!$B$2:$B$1489,Date(2019,7,1+1),'H2no'!$M$2:$M$1489)

Kindly assist me on this. Thank you.

- Prashanth July 26, 2019 At 1:47 pm
  
  Hi, Vidya,
  
  You can try this Query formula.
  
  =Query(H2no!$B$2:$M$1489,"Select B, avg(M) where B is not null group by B")
  
  This formula will produce/populate an expanding array result. So make sure you leave blank rows and two blank columns for this formula output.
  
  If you want me to enter this formula, please share an example Sheet with me.
  
  Best,
  
Christian S July 9, 2019 At 9:19 am

Hi Prashanth, I have a related question that hopefully, you can help with.

I have the following formula:

=sumifs('Ticket Level Tracking Sheet'!$I$2:$I$270,'Ticket Level Tracking Sheet'!$A$2:$A$270,$A6,'Ticket Level Tracking Sheet'!$J$2:$J$270,">4/30/19",'Ticket Level Tracking Sheet'!$J$2:$J$270,"<7/6/19")

I would like to use a cell, e.g. G1 or G2, instead of “>4/30/19” or “<7/6/19".

- Prashanth July 9, 2019 At 10:09 am
  
  Hi, Christian S,
  
  Please see the SUMIFS date criteria usage tips here – How to Include a Date Range in SUMIFS in Google Sheets.
  
  Best,
  
Kristian Sauva May 26, 2019 At 3:31 pm

Thank you so much! Worked wonders!!!

Kristian Sauva May 26, 2019 At 10:45 am

Hi,

I need help on this one

=QUERY('Raw Data New'!A:BS,"select A,BN,BO,BQ,BR,BP,BM,H,AA,AB,AH,AI,AJ,AK,AL,AM,AN,X,Y,AQ,AR,AS,AT,AU,AV,AW where BP = date '"&TEXT($AN$7,"yyy-mm-dd")&"'")

AN7 is the cell reference date and I need pull all dates that include cell reference plus 7 days or 1 week prior to the cell reference date.

Thank you!

- Prashanth May 26, 2019 At 11:20 am
  
  Hi, Kristian Sauva,
  
  Use the date criteria in Query with the simple comparison operators as below.
  
  Formula to pull rows based on the date in cell AN7 plus 7 days.
  
  =QUERY('Raw Data New'!A:BS,"select A,BN,BO,BQ,BR,BP,BM,H,AA,AB,AH,AI,AJ,AK,AL,AM,AN,X,Y,AQ,AR,AS,AT,AU,AV,AW where BP>= date '"&TEXT($AN$7,"yyy-mm-dd")&"' AND BP<= date '"&TEXT($AN$7+7,"yyy-mm-dd")&"'")
  
  Formula to pull rows based on the date in cell AN7 minus 7 days.
  
  =QUERY('Raw Data New'!A:BS,"select A,BN,BO,BQ,BR,BP,BM,H,AA,AB,AH,AI,AJ,AK,AL,AM,AN,X,Y,AQ,AR,AS,AT,AU,AV,AW where BP>= date '"&TEXT($AN$7-7,"yyy-mm-dd")&"' AND BP<= date '"&TEXT($AN$7,"yyy-mm-dd")&"'")
  
SAIRAM SEKARAN May 7, 2019 At 8:38 am

I have a data set in this format:

Col1 Col2 Col3
26/04/19 10:10:00 0.03 0.037
26/04/19 16:30:00 0.03 0.037

And I wish to query the latest 7 days data.

The catch is this google sheet is not static. It’s dynamic and gets updated every day.

- Prashanth May 7, 2019 At 11:27 am
  
  Hi, SAIRAM SEKARAN,
  
  Please try this filter.
  
  =filter(A1:C,gte(int(A1:A),today()-7),lt(int(A1:A),today()))
  
  Best,
  
Peter Snow April 23, 2019 At 7:05 am

Hi Prashanth,

I’m trying your method above but am getting an empty resultset returned when I know there is data. The query I’m using is:

=query(ImportedData1!$A$1:$AC, "Select avg(AC) where A = DATE'"&TEXT($A67, "yyyy-mm-dd")&"' label avg(AC) '' ", 0)

I know that the data has already imported as the following in the next column is working:

=query(ImportedData1!$A$1:$CX, "Select avg(AG) where month(A)=month(date'"&text($A67,"yyyy-MM-dd")&"') AND year(A)=year(date'"&text($A67,"yyyy-MM-dd")&"') label avg(AG) '' ", 0)

A67 = “2019-03-08” and the cell is in a date format.

The first formula is to get the results for a specific day 8th March 2019, whereas the second is to get the result for the whole month of March 2019.

I’d love to know what I’m doing wrong?

Thanks.

- Prashanth April 23, 2019 At 11:22 am
  
  Hi, Peter Snow,
  
  I created a sample data and tested both of your formulas. I could see that both of them working as intended.
  
  So please make sure that whether you are correctly using the average column in your formula. Is that AC or AG?
  
  Best,
  
dashi January 22, 2019 At 11:42 am

Hi,
Suppose I have dates and number of items for each date. What I notice is that the date recurs 3 times with the different number of items. So how do I use Query to put one date with the sum of the items?

- Prashanth January 22, 2019 At 12:03 pm
  
  Hi, Dashi,
  
  You may need to use the group by and Pivot clause in Query.
  
  Here is one example.
  
  =query(A1:C,"Select A, sum(C) where A is not null group by A pivot B",1)
  
  The sample data for this formula is like this.
  
  Column A contains the date, B the items, and C the Qty. Also, the first row contains the field labels.
  
  Hope this may help.
  
  Best,
  
Mani January 19, 2019 At 9:05 pm

Hi, Good information on your site. I am having trouble getting a calculated date part of the select statement. Is this possible in the query?

Ex.: query(Transactions!A:G,"Select A+30,G order by A DESC",0).

Here column A is a date and I was expecting to get date+30 days, but it doesn’t give any results.

Thanks for your help!

- Prashanth January 20, 2019 At 12:59 pm
  
  Hi, Mani,
  
  As far as I know, you can’t sum a date in the Query select clause. See this tutorial for more details – How to Use Arithmetic Operators in Query in Google Sheets.
  
  Better use the formula as below.
  
  =Query({ArrayFormula(to_date(if(len(Transactions!A1:A), Transactions!A1:A+30,""))), Transactions!B1:G},"Select Col1,Col7 ")
  
  Cheers!
  
  - Mani January 26, 2019 At 8:39 am
    
    Great advice. Thanks, Prashanth.
    
    - Prashanth January 26, 2019 At 9:38 am
      
      Welcome!
      
EvoJanus January 10, 2019 At 11:09 pm

How about order by DateTime?

My source data is sorted by date descending. I would like my query function to order by DateTime Asc.

However, as I use these formulas as you have laid out, the output for DateTime shows the actual date with literal zeros for time. ex: 1/9/2019 0:00:00. So if I have two or more entries on the same day, it doesn’t change the order since each entry’s time is 0:00:00. So the order defaults to source data order.

Please assist

- Prashanth January 11, 2019 At 9:14 am
  
  Hi, EvoJanus,
  
  I hope the below formula can shed some light on this issue.
  
  =query(sourcemaster,"select A,B,C,D,E,F where F>= datetime '2019-1-11 10:00:00' and F<= datetime '2019-1-11 14:00:00' order by F Asc")
  
  Also, you can refer to this related tutorial.
  
  How to Filter Timestamp in Query in Google Sheets
  
  Cheers!
  
Sarah November 20, 2018 At 9:40 pm

I’m having a really tough time getting a Query formula to show information selected between two dates. Currently, my formula looks like this:

=query ( Dataset, "select C, S, V, Y, AC, AH, AL, AP, AU, AX, BC, BG, BL, BO, BR where C >= Date '"&TEXT(DateValue("2018-10-01"),"yyyy-mm-dd")&"'and C <= Date ' "&TEXT(DATEVALUE("2018-11-01"), "yyyy-mm-dd") &" ' ", 1)

It's giving me this error:

Error

Unable to parse query string for Function QUERY parameter 2: Invalid date literal [ 2018-11-01 ]. Date literals should be of form yyyy-MM-dd.

Help! The date is in the right format. Am I crazy?

- Prashanth November 21, 2018 At 8:23 am
  
  Hi, Sarah,
  
  You may simply use the formula as below.
  
  =query(dataset, "Select C,S,V,Y,AC,AH,AL,AP,AU,AX,BC,BG,BL,BO,BR where C>= date '2018-10-01' and C<= date '2018-11-01'", 1)
  
  If not working, if possible, try to make a copy of the sheet and remove all the sensitive/confidential info and share with me.
  
  Update:
  
  In my formula, please correct C< = date to C<= date. When I publish the comment, the comment page in the theme inserts an extra space.
  
Kevin November 18, 2018 At 5:16 pm

Hi,

This looks just what I am looking for with example 2, but when I try this out for myself it only pulls the headers into the other sheet. I am confused as all I have done is copy the actual data and the query from your website to test it out for myself before modifying it for my project. Has something changed to the way google sheets handles the formula?

- Prashanth November 18, 2018 At 6:17 pm
  
  Hi,
  Share that Sheet if possible.
  
Sebastian September 7, 2018 At 8:38 pm

Hi Prashanth,

Thank you again for your quick replay and willingness to help.

I have tried but didn’t work out for me and also I would prefer the query. I think I didn’t explain the case correctly.

I have two tables.

Date 1st email sent Date 2nd email sent
23.08.2018
24.08.2018 02.09.2018
25.08.2018 03.09.2018
26.08.2018
27.08.2018 05.09.2018
28.08.2018 06.09.2018
29.08.2018
11.07.2018 02.08.2018
07.08.2018 14.08.2018
01.09.2018 03.09.2018
02.09.2018 11.09.2018
03.09.2018 06.09.2018
04.09.2018
I would like to create the query that counts how many times the second email wasn’t sent and passed already 10 days, and another query that counts how many times 2ed email was sent after the due time 10 days.

I am really sorry that I didn’t explain it like that first time.

Thank You again

- Prashanth September 7, 2018 At 9:36 pm
  
  Hi, Sebastian,
  
  Thanks for posting the details.
  
  This time instead of presenting my formulas here, I’ve prepared a sample spreadsheet for you and other users who are interested.
  
  Link to Query Sheet
  
  - Sebastian September 10, 2018 At 2:48 pm
    
    Hi Prashanth,
    thank you!! You helped me a lot!
    
Sebastian September 5, 2018 At 3:13 pm

Hi,

First of all, thank you for this website, you are doing a great job.
How can I write a syntax that fills a column after 10 days or longer from the date that is in that column?

Thank you in advance

- Prashanth September 5, 2018 At 4:53 pm
  
  I didn’t get! Can you elaborate little more?
  
  - Sebastian September 5, 2018 At 7:14 pm
    
    Sorry. I have a col A where we write a date, in 10 days another department should write a today date in col B. I want to count how many times another dep. didn’t write a date in col B till 10 days after we did in col A. I hope that now its clear and again sorry.
    
    - Prashanth September 5, 2018 At 10:37 pm
      
      Hi, Sebastian,
      
      I think I have got some idea.
      
      I guess you have the column range A1:A filled with dates. In column range B1:B, against each date in column A, you expect the dates entered and that dates should be +10 days to the date in your first column.
      
      What you want is the count of column range B1:B with dates not equal to Colum A date + 10 days.
      
      See if this helps?
      
      =count(A1:A)-ArrayFormula(countif(if(len(A1:A),DATEDIF(A1:A,B1:B,"D"),),10))
      
      Thanks.
      
needyneedy August 28, 2018 At 8:11 pm

I need to use a query where I want to fill a column with future date depended on another column where it should only pick the dates which are “today” and in the “future”.

How do I have to build the query syntax in that?

Appreciate any useful help.

- Prashanth August 29, 2018 At 4:36 pm
  
  Hi,
  
  Suppose your column A contains the date and you want the Query to populate the dates in another column which is today or greater than today. Then you can use this Query formula.
  
  =query(A1:A,"Select A where A >= date '"&TEXT(today(),"yyyy-mm-dd")&"'")