Filter the Last N Days of Data in Google Sheets

Published on

The easiest way to filter the last N days of data in Google Sheets is by using the FILTER function in combination with the ISBETWEEN function. Additionally, you may need to use the TODAY and MAX functions, depending on your specific use case.

Below, you will find three formulas, each handling a different scenario. These formulas filter the last N days of data based on:

  1. Today’s date (rolling window approach)
  2. A user-defined date
  3. The maximum date in the dataset

Sample Data

The sample dataset consists of:

  • Column A: Dates
  • Column B: Items (fruit names)
  • Column C: Quantities

The filtering range is A2:C, excluding the header row.

Filter Last N Days from Today (Rolling Window Approach)

The following formula filters the last 7 days of data from today:

=FILTER(A2:C, ISBETWEEN(A2:A, TODAY()-7, TODAY()-1))
Example of filtering the last N days from today

Formula Explanation:

  • A2:C is the range to filter.
  • ISBETWEEN(A2:A, TODAY()-7, TODAY()-1) is the condition.
  • The formula filters rows where the condition is TRUE.
  • The ISBETWEEN function checks if each date in A2:A falls between TODAY()-7 and TODAY()-1.

This formula excludes today’s date. If you want to include today, modify it as follows:

=FILTER(A2:C, ISBETWEEN(A2:A, TODAY()-6, TODAY()))

Filter Last N Days from a Specific Date (User-Defined Date)

The formula above dynamically adjusts based on TODAY(), making it a rolling window approach. However, you might need to filter data relative to a specific date instead.

To do this, replace TODAY() with a user-defined date using the DATE(year, month, day) function. For example, to filter the last 7 days from 12/02/2025:

=FILTER(A2:C, ISBETWEEN(A2:A, DATE(2025, 2, 12)-7, DATE(2025, 2, 12)-1))
Example of filtering the last N days from a specific date

Key Notes:

  • This formula filters data for the last 7 days relative to 12/02/2025.
  • The filtered data excludes 12/02/2025. To include it, modify the formula as follows:
=FILTER(A2:C, ISBETWEEN(A2:A, DATE(2025, 2, 12)-6, DATE(2025, 2, 12)))

This formula is useful for historical data analysis.

Filter Last N Days from the Latest Date in the Data

In cases where your dataset is irregularly updated, you may not have today’s date or a specific reference date. Instead, you can filter data based on the latest date in your dataset using the MAX function.

Here’s how:

=FILTER(A2:C, ISBETWEEN(A2:A, MAX(A2:A)-7, MAX(A2:A)-1))
Example of filtering the last N days from the latest date

Formula Explanation:

  • MAX(A2:A) finds the latest date in column A.
  • The formula then filters the last 7 days from this latest date.

This excludes the latest date. To include it, modify the formula as follows:

=FILTER(A2:C, ISBETWEEN(A2:A, MAX(A2:A)-6, MAX(A2:A)))

This approach is ideal for datasets that are updated at irregular intervals.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.