1

I am using query and importrange function to collect data from another Google sheet, I am filtering by the Publisher and the user-specified Start Date (F1) and End Date (G1). What I have right now works just for the start date, except that I haven't been able to filter it between the two dates. So I would like to keep all of the data that has fallen within the start date or the end date. Start date is Col9 and End Date is Col10 in the source data

The next step for this would be to change the start date and end date if it is less than or greater than the user-specified dates. For example, if the start date based on the importrange function is July 15th, but the user-specified date is August 1st then I want the cell with that date to be August 1st. The same logic applies to the end date, except any end date greater than the user-specified date should be changed to August 31st in this case. Is including this aspect in a single formula possible? Or would I have to create a separate query/script?

Below is a link to a copy of the formula I'm working with: https://docs.google.com/spreadsheets/d/1hdE2q8UDdStx98WH9de3PljiMZzasZzhBChUAxLPgGQ/edit#gid=28163102

Below is the source data: https://docs.google.com/spreadsheets/d/1N8m_YtO1Nc5AXSCAvT5Z1RiKs30bwjt6XDk9zA5QSOE/edit?usp=sharing

If I remove " and Col10 <= date '"&text(G1,"yyy-mm-dd")&"' " from the code, it works with the start date. 

=QUERY(importrange("https://docs.google.com/spreadsheets/d/1N8m_YtO1Nc5AXSCAvT5Z1RiKs30bwjt6XDk9zA5QSOE/edit#gid=0","Activity Tracker - Publisher Co!B10:K"),"Select Col6, Col1, Col2, Col4, Col9, Col10 where Col2 is not null and Col9 is not null and Col1 contains 'Publisher 1' and (Col9 >= date '"&text(F1,"yyy-mm-dd")&"' and Col10 <= date '"&text(G1,"yyy-mm-dd")&"')")
Improve this question

1 Answer 1

Reset to default
1

try:

=QUERY(IMPORTRANGE(
 "1N8m_YtO1Nc5AXSCAvT5Z1RiKs30bwjt6XDk9zA5QSOE", 
 "Activity Tracker - Publisher Co!B10:K"),
 "select Col6,Col1,Col2,Col4,Col9,Col10 
  where Col2 is not null 
    and Col9 is not null 
    and Col1 contains 'Publisher 1' 
    and Col9  <= date '"&TEXT(G1, "yyyy-mm-dd")&"' 
    and Col10 >= date '"&TEXT(F1, "yyyy-mm-dd")&"'", 0)

0

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Hi, thank you so much for your help! Unfortunately, this is not the desired outcome. I need to be using Col9 for the "Actual Start Date" I think this formula is only returning values when it fits both Col9 and Col10 criteria. I was experimenting it with other publishers and found that I was getting an #N/A! error if either the start date and end date did not fit the criteria. So I'm guessing I need an Or statement instead of an AND statement?
The issue with that is in rows 4-7 the formula is grabbing start dates that do no fall within the specified range. I need the Start Date or the End date to fall within August 1st-30th in this case. So the minimum start date must be August 31st and the the maximum end date must be August 1st
well this is not an issue of wrong syntax. its just that your table does not match given criteria so query outputs nothing. see here this proof that it works correctly: i.sstatic.net/kkrmY.png
Sorry, I don't think I'm communicating my goal properly. The content may not have a start date or end date that is in August. For example, if something ran from July 15th to September 15th I would still want to include it in the report because it still ran throughout August. This is my desired results based on the August 1st - 30th criteria. imgur.com/a/ysq1qVa So a maximum start date of August 31st and a minimum end date of August 1st as the criteria.
That's the ticket, thank you for your patience! Much appreciated

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.