CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulagoogle-query-language
Sairam SEKARAN
Sairam SEKARAN

Reputation: 35

Query Latest 7 days in google sheet

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
26/04/19 22:01:00   0.03    0.037
27/04/19 04:15:00   0.03    0.037
27/04/19 10:15:00   0.03    0.037
04/05/19 01:15:00   0.03    0.037
04/05/19 06:05:00   0.03    0.037
04/05/19 11:12:00   0.03    0.037
04/05/19 16:21:00   0.00    0.037
04/05/19 22:27:00   0.03    0.037
05/05/19 04:35:00   0.03    0.037
05/05/19 10:31:00   0.03    0.037
05/05/19 16:13:00   0.03    0.037
05/05/19 22:05:00   0.03    0.037
06/05/19 04:10:00   0.00    0.037

And I wish to query the latest 7 days data. In this case will be the latest 7 days data from 06/05/19 04:10:00. which is until 31/4/19 04:10:00. So I wish to query all data from 31/4/19 04:10:00 to 06/05/19 04:10:00.

Expected Outcome: 

Col1                    Col2    Col3
04/05/19 01:15:00   0.03    0.037
04/05/19 06:05:00   0.03    0.037
04/05/19 11:12:00   0.03    0.037
04/05/19 16:21:00   0.00    0.037
04/05/19 22:27:00   0.03    0.037
05/05/19 04:35:00   0.03    0.037
05/05/19 10:31:00   0.03    0.037
05/05/19 16:13:00   0.03    0.037
05/05/19 22:05:00   0.03    0.037
06/05/19 04:10:00   0.00    0.037

The catch is this google sheet is not static. It's dynamic and keeps getting updated. The dates are always in order but can have more than 1 entry per date.

Formulas I have tried so far:

=SORT(QUERY(A14:C,"order by A desc limit 7"),1,1)

and

=QUERY(A14:C,"Select * where A >= date '"&TEXT(INDEX(SORT(A14:C,1,false),MATCH(true,ISNUMBER(INDEX(SORT(A14:C,1,false),,2)),0),1)-6,"yyyy-mm-dd")&"' limit 7")

The 1st formula gives me a blanket last 7 dates in my range. The 2nd formula is specific for date and is only feasible when there are no date duplications. Hence both the formulas don't fit my needs

Google Sheet Data

Upvotes: 1

Views: 2381

Answers (2)

player0
player0

Reputation: 1

=QUERY({A:C}, "where Col1 <= date '"&TEXT(TODAY(),   "yyyy-MM-dd")&"' 
                 and Col1 >  date '"&TEXT(TODAY()-7, "yyyy-MM-dd")&"'", 0)

0

=ARRAYFORMULA(QUERY({A2:C, DATE(20&MID(A2:A, 7, 2), MID(A2:A, 4, 2), LEFT(A2:A, 2))},
 "select Col1,Col2,Col3
  where Col4 <= date '"&TEXT(TODAY(),   "yyyy-MM-dd")&"' 
    and Col4 >  date '"&TEXT(TODAY()-7, "yyyy-MM-dd")&"'", 0))

0

Upvotes: 0

JPV
JPV

Reputation: 27282

See if this works

=sort(query(A14:C, "Where A is not null order by A desc limit 7"), 1, 1)

Alternatively, you can also try

=offset(A1, counta(A:A)-7, 0, 7, 3)

Upvotes: 1

Related Questions