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”)&””””)

3 Answers 3

2

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")&"'")

4
  • sorry says parameter 2 is wrong :/ Aug 14, 2014 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, 2014 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? Aug 15, 2014 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, 2014 at 20:18
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' ")
2
  • should that be B1 then B2? in the if. i cant work out what your asking it Aug 14, 2014 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 :/ Aug 15, 2014 at 12: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
  • 1
    Some explanation on what's going on here would make this a better answer.
    – ale
    Mar 23, 2018 at 16:55

Your Answer

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

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