CODEWITHSUNDEEP
google-sheets-formula
Stuart
Stuart

Reputation: 325

Query importrange within date parameters and separate time parameters returning percentage

I have created this formula that should count all rows from another sheet with the specific mentioned value with date range => & =< and with time range => & =<

It returns 0 regardless of what values I test with...

    =COUNTA(IFERROR(QUERY({IMPORTRANGE("/1xNFVHLnQGkRgZdLmejCyU0BByOPBY8NMoIYj6SkTFGY/edit#gid=431567503", "July!A2:C")}, 
                       "Select Col1 where Col1 contains '"&A3&"' 
                                      and Col2 >= date '2022-07-01' 
                                      and Col2 <= date '2022-07-31'
                                      and Col3 >= time '18:00'
                                      and Col3 <= time '08:00'" ,0)))

Data: https://docs.google.com/spreadsheets/d/1xNFVHLnQGkRgZdLmejCyU0BByOPBY8NMoIYj6SkTFGY/edit#gid=431567503

Testing sheet: https://docs.google.com/spreadsheets/d/1xNFVHLnQGkRgZdLmejCyU0BByOPBY8NMoIYj6SkTFGY/edit#gid=1075801741

In the ideal world I would want to take this a step further and instead of a count of this data it would return a percentage of all the data based on the partner name in Sheet2! '"&A3&"'

Update 1 step closer: =COUNTA(IFERROR(QUERY({IMPORTRANGE("/1xNFVHLnQGkRgZdLmejCyU0BByOPBY8NMoIYj6SkTFGY/edit#gid=431567503", "July!A2:D")}, "Select Col1 where Col1 contains '"&A3&"' and Col4 >= datetime '"&TEXT(H1,"e-MM-dd HH:mm:ss")&"' and Col4 <= datetime '"&TEXT(H2,"e-MM-dd HH:mm:ss")&"'",0)))

This formula returns data that is within a specific time and date range and has a particular value in a column.

The way I worded the original question would mean I am now almost there.

Upvotes: 0

Views: 170

Answers (1)

player0
player0

Reputation: 1

there is no time. go for datetime:

enter image description here

update:

D2:

=ARRAYFORMULA(IF(A2:A="",,B2:B+C2:C))

G4:

=COUNTA(IFERROR(QUERY({IMPORTRANGE(
 "1WsQQJ3qWM_m-nHW6R4xhewgQTfN5haZ61eJx1KkgaSg", "July!A2:D")}, 
 "select Col1 
  where Col4 >= datetime '"&TEXT(H1, "e-MM-dd HH:mm:ss")&"'
    and Col4 <= datetime '"&TEXT(H2, "e-MM-dd HH:mm:ss")&"'", 0)))

enter image description here

demo sheet

Upvotes: 1

Related Questions