In this post, let’s learn how to filter rolling n days from a date range in Google Sheets. It means filtering a certain number of dates (date range) from a moving date range. Instead of rolling n dates, you can filter a dataset by rolling n months too in Google Sheets.

Rolling n days means a certain number of days from today to backward in which today will be the current day, not any specific date. In short previous n days from the current day.

If today is 12-Apr-2020, rolling 10 days will be the dates from 03-Apr-2020 to 12-Apr-2020 (total 10 days). It will change based on today’s date. That’s why it’s called a rolling date range.

In another example, if today’s date is 31-Dec-2020, rolling 31 days will be the dates from 01-Dec-2020 to 31-Dec-2020.

QUERY Formula to Filter Rolling 30 | 90 | 120 Days in Google Sheets

We can use a Query or a Filter formula to filter rolling n days in Google Sheets.

I am going to explain the Query part as you may want to summarize your filtered data too (I’ll explain the summary part in another tutorial though).

I have a sample dataset in the following format in one of my Google Sheets file.

It’s just part of the data. There are data in several rows (data range A1:C).

To filter the last 30 days data, I mean rolling 30 days data, we can use the following Query.

Rolling 30 Days:

Formula # 1:

=Query(
    {A1:C},
      "Select *
        Where Col1 > date '"& text(TODAY()-30,"yyyy-mm-dd") & "'
        and Col1 <= date '"& text(today(),"yyyy-mm-dd") &"'",1
)

Must Read: Examples to the Use of Literals in Query in Google Sheets.

Rolling 90 Days:

To filter the data for the last 90 days, I mean rolling 90 days, in Google Sheets, change 30 in the formula to 90.

Rolling 120 Days:

I think it goes without saying.

Note: If the date column contains timestamp aka DateTime, then the above formula might not work. In that case, if you face any issue, please refer to this guide – How to Use DateTime in Query in Google Sheets.

Additional Tip: Filter Menu and Conditional Formatting to Filter Rolling N Days

Instead of using a Query formula as above, you can use the filter menu to filter the data for the last n days. Then copy the filtered data to the next sheet. Here is how.

1. Select column A (or the column contains the dates).
2. Click on the Data menu ‘Create a filter’.
3. Click the drop-down in cell A1 (filtered column) and select Filter by condition > Is between.
4. Then follow the below screenshot.

Here we can follow a different approach too. We can use the same above filter settings in the conditional formatting which will highlight rolling 30 days.

Then in the Filter menu, instead of the ‘Filter by condition’, use the Filter by color option. I hope you could understand what I am trying to say.

I don’t suggest this conditional formatting method if your data is very large as it may slow down your sheet’s performance.

QUERY Formula to Filter Rolling N Months in Google Sheets

To filter a dataset based on rolling n months in Google Sheets we can use the Query again. But you should have a clear idea about what you really want to filter. Don’t worry! I’ll explain.

Scenario 1:

Assume today’s date is 12-Apr-2020. I want to filter rolling n months’ data in Google Sheets. Let’s consider ‘n’ as 1.

Want I really want is not the data from 01-Apr-2020 to 12-Apr-2020. I want a full month’s data. So my rolling one month’s data will be from 01-Mar-2020 to 31-Mar-2020.

If today’s date is 30-Apr-2020, I want the data from 01-Apr-2020 to 30-Apr-2020, otherwise the last month’s data.

Scenario 2:

On the contrary to the above, some of you may want partial ‘n’ months’ data too.

That is rolling 1 month is the data from 01-Apr-2020 to 12-Apr-2020 and 2 months is the data from 01-Mar-2020 to 12-Apr-2020.

This, scenario 2, is the easiest one to implement. So, first, I am going to start with the formula to filter rolling n months, irrespective of the partial or full current month, in Google Sheets.

Partial or Full Current Month

Let me start with the normal rolling month formula in Google Sheets which will filter the data in the current month irrespective of full or partial month.

Formula # 2: Rolling 1 Month.

=Query(
    {A1:C},
      "Select * Where month(Col1)=month(now())
       and year(Col1)=year(now())",1
)

How to filter last two (rolling) months data?

If your data has spread across the years and when there are more than one month’s data to filter, then you may face issues in rolling month filtering. You may need to specify the years dynamically.

As per the above formula, using the formula year(now()) we could find the current year. But what about the year two months back or n months’ back? In rolling months we can’t predict that, right?

So we can follow this logic. Instead of specifying month and year, let’s specify it as per the below logic.

Logic: Filter the data between the date range;

>= month_start_date_of_the_Nth_month and <= today's_date

To find the month_start_date_of_the_Nth_month we can use the below formula.

=eomonth(today(),-n)+1

Note: If you are new to EOMONTH or other date functions in Sheets, please refer to this tutorial – How to Utilize Google Sheets Date Functions [Complete Guide].

Based on the above logic, let’s filter the rolling 3 months’ data in Google Sheets.

Formula # 3: Rolling 3 Months.

=Query(
     {A1:C},"Select * where Col1>=date '"&TEXT(eomonth(today(),-3)+1,"yyyy-mm-dd")&"'
     and Col1<=date '"&TEXT(now(),"yyyy-mm-dd")&"'"
)

Take a note of the ‘n’ which is -3 in the formula. Change that to -1 for rolling 1 month, -6 for rolling 6 months, etc.

Additional Tip: Filter Menu to Filter Rolling N Months in Google Sheets

Earlier I have written how to filter by month using the filter menu in Google Sheets. To filter by rolling n months using the menu command, you can follow the below steps.

Refer to the screenshot below to see the formulas to use in the ‘Filter by condition’. The following formulas (highlighted) filter the data by the rolling 6 months.

Skip Current Month If Today Is Not End of the Month (Whole Months Only)

I have already explained the whole rolling month concept at the beginning (scenario 1). The following tips are based on that concept.

Here we can filter the data between the below date range.

Rolling month from (‘-n’ determines the starting month):

=edate(
     if(today()=eomonth(now(),0),
        today(),
        eomonth(today(),-1))+1,
     -n
)

Rolling month to:

=if(
     today()=eomonth(now(),0),
        today(),
        eomonth(today(),-1
     )
)

As per the above, here is the rolling month formula to filter rolling 3 full months’ data in Google Sheets.

=Query(
     {A1:C},"Select * where Col1>=date 
        '"&TEXT(
           edate(if(today()=eomonth(now(),0),today(),eomonth(today(),-1))+1,-3),
        "yyyy-mm-dd")&"'and Col1<=date 
        '"&TEXT(
           if(today()=eomonth(now(),0),today(),eomonth(today(),-1)),
        "yyyy-mm-dd")&"'"
)

That’s all. Enjoy!

Resources:

• How to Calculate the Simple Moving Average in Google Sheets.
• How to Calculate the Moving Sum of Current Week in Google Sheets.
• SUMIF Formula to SUM Current Week’s Data in Google Sheets.
• How to Find Current Month’s Week Number in Google Sheets.
• Formula to Sum by Current Week in Google Sheets.
• SUMIF to Sum By Current Work Week in Google Sheets.
• Query to Sum Current Work Week Range in Google Sheets.
• Sum Current Month Data Using Query Function in Google Sheets.
• Filter or Find Current Week This Year and Last Year in Google Sheets.