2

I have a Google Spreadsheet that I'm querying and the date is set as dd/mm/yyyy.

How can I query between the start and finish (ref cells) of each month, so the query only returns dates between the ref cells.

This is what I was trying to re-code:

=QUERY('Calculated Pay [view]'!B1:B1000,”select * where B>=date”””&text(A2,”dd/mm/yyyy”)&””” and B<=date”””&text(B2,”dd/mm/yyyy”)&””””)
1

Might be easier to add a column C with a formula such as:

=if(and(B1>eomonth(Sheet2!$A$2,-1),B1<=eomonth(Sheet2!$A$2,0)),"s",) 

and apply a simplified query such as:

=query('Calculated Pay [view]'!B1:C1000,"Select B where C ='s' ")
  • should that be B1 then B2? in the if. i cant work out what your asking it – Showcase Imagery Aug 14 '14 at 16:36
  • with your solution i get results but for july, not august. i thought changing the -1 to 1 or 0 might work....didnt :/ – Showcase Imagery Aug 15 '14 at 12:18
1

QUERY expects dates to be in yyyy/MM/dd format:

=QUERY('Calculated Pay [view]'!B1:B1000,"select * where B >= date '"&TEXT(A2,"yyyy-MM-dd")&"' and B <= date '"&TEXT(B2,"yyyy-MM-dd")&"'")

  • sorry says parameter 2 is wrong :/ – Showcase Imagery Aug 14 '14 at 16:35
  • Are you able to enter into a spare cell a formula that just returns the query string, and post the output here? ie ="select * ..." – AdamL Aug 14 '14 at 19:13
  • 1
    sorry, tried that but didnt know how to get a result. this is the answer from hovering over #VALUE. Unable to parse query string for Function. QUERY parameter 2: Invalid date literal [2014/08/01]. Date literal should be of form yyyy-MM-dd. does that mean the dates being formatted backwards? – Showcase Imagery Aug 15 '14 at 12:26
  • Oh gosh, I'm really sorry, as per the error message you quoted, the date format requires hyphens, not slashes. Answer edited. Truly a derp moment. – AdamL Aug 15 '14 at 20:18
-1
=round(query(po, 
             "select count(AE)/count(Z)
              where Z>date'"&I6&"' 
              and Z<=date'"&J6&"' AND W='"&B7&"' 
              label count(AE)/count(Z)''")*100-100, 2) & "%"
  • 1
    Some explanation on what's going on here would make this a better answer. – ale Mar 23 '18 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.