How to Filter Last 30 Days of Date in Excel (5 Easy Ways)

Suppose we have the following dataset containing dates along with other entries, and we want to filter the dataset to only show entries from the last 30 days.

Dataset-Excel Date Filter Last 30 Days

In this article, we’ll demonstrate how to use the Filter feature, VBA Macro, TODAY function, the FILTER function as well as the Power Query to accomplish this task.

Method 1 – Using the Filter Feature

To date-filter a certain number of days, we can use the Filter option Between.


  • Place your cursor in any cell.
  • Go to the Home tab > Editing section.
  • Click on Sort & Filter.
  • Click on Filter.

Filter Option-between-Excel Date Filter Last 30 Days

Excel enables the Filter feature for the columns.

  • Click on the Filter Icon.
  • Select Date Filters.
  • Click on the option Between.


The Custom AutoFilter window opens.

  • Under Show rows where, select respective dates for is before or equal to and is after or equal to using the Calendar icons.
  • Click OK.

AutoFilter window

Excel filters the worksheet’s entries to those within the last 30 days.


Similarly, filter the entries for the last 60 or 90 days, or as desired.

  • Alternatively, apply the Filter feature by going to the Data tab > Sort & Filter section > Filter.

Alternative Filter option

Method 2 – Using Excel VBA

VBA Macros allow users to write their own functions, as we will do here with a sub-procedure called FilterToLastNDays.

VBA-Excel Date Filter Last 30 Days


  • Press ALT+F11 to open the Microsoft Visual Basic window.
  • Alternatively, go to the Developer tab > Visual Basic > Insert > Module.

Module Insertion

  • Copy the following code and paste it in the Module window:
Sub FilterLast30Days()
FilterToLastNDays 30
End Sub
Sub FilterToLastNDays(N As Integer)
With Worksheets("VBA").Range("$B$4")
.AutoFilter Field:=1, Criteria1:=">=" & Date - N, Operator:=xlAnd, Criteria2:="<=" & Date
End With
End Sub


In the code, the first macro holds the FilterToLastNDays function to filter the last 30 days, then the second macro defines the assigned function. The WITH statement provides the worksheet and ranges for filtering. The AutoFilter command assigns the necessary criteria to execute the filtering.

  • Press F5 to run the macro.
  • Return to the worksheet.

Excel filters the entries according to the given criteria.

VBA Outcome-Excel Date Filter Last 30 Days

Simply edit the number of days after the FilterToLastNDays function to change the filter range.

Method 3 – Using AND and TODAY Functions

The AND function takes multiple logic as its arguments. We’ll insert the TODAY function as an argument to assign dates as criteria.


  • Add a helper column adjacent to the dataset.
  • Copy and paste the following formula in cell E5:

Today function-Excel Date Filter Last 30 Days

The ADD function takes B5>=(TODAY()-30 as its Logical1 and TODAY() as Logical2 arguments.

  • Press ENTER to return the result.
  • Drag the Fill Handle down to display the logical outcomes of TRUE or FALSE in the rest of the column.

Helper Column

  • Apply the Filter feature by following the first Step of Method 1.
  • Click on the Filter Icon.
  • Mark only the TRUE option.
  • Click on OK.

Filter Options

Excel displays only the entries within the last 30 days.


Method 4 – Using the FILTER Function

The FILTER function is only available in the Excel 365 version.

The syntax of the FILTER function is:

FILTER (array, include, [if_empty])


  • Enter the following formula in cell F4:

FILTER Function-Excel Date Filter Last 30 Days

In the formula, B4:D16 is the array, B4:B16>=TODAY()-30 is the function to include and B4:B16<TODAY() is the function to run [if_empty].

  • Press ENTER to fetch all the entries that match the criteria.

FILTER Function Outcome

Method 5 – Using Power Query

The in-built Power Query tool also offers a Filter Feature.


  • Highlight the range.
  • Go to the Data tab.
  • Click on From Table/Range (under Get & Transform Data).

Power Query-Excel Date Filter Last 30 Days

Excel opens a Create Table window and automatically sets the range.

  • Tick the My table has headers option.
  • Click on OK.

Create Table

Excel loads the data.

  • Click on the Filter Icon of the Order Date column.
  • Click on Date/Time Filters > Between.

Filter Options-Excel Date Filter Last 30 Days

Excel opens the Filter Rows dialog box.

  • Choose And logic.
  • Provide dates to encompass 30 or the desired number of days.
  • Click on OK.

Filter Rows

Excel loads the filtered data.

  • Click Close & Load.
  • Click Close & Load again.

Close & Load

The filtered data are loaded into a new worksheet.

Power Query Outcome

Download Excel Workbook

<< Go Back to Date Filter | Filter in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

  1. Selecting the dates manually locks in that date range and you have to figure in your head what days to select and adjust it every time. There should be a way to enter a formula like =today()-7 in the value field of the Custom Autofilter dialog but this results in all dates being excluded, which brings me back to looking all over the place online to find a solution that is intuitive and does not involve VBA or hidden cells. I can do all that, but Microsoft should have included a more intuitive option in this custom filter that supports using simple formulas in these fields. You can’t even enter a reference to a cell that calculates the start date for the filter. I’m using Office 2016 for reference by the way.

    • Greetings Joseph Clovis,

      The AND and FILTER functions do take cell references to filter and fetch entries that fall under the condition (Date>= T0DAY()-30 <TODAY()) respectively. However, sadly, the Excel Filter feature doesn’t support selecting dates from ranges or entries. All the Filter execution does is that it hides rows, which may lead to inconveniences sometimes. Till now, there has been no alternative to selecting dates from the Filter dialog box options. But you can try Conditional Formatting or VLOOKUP function for highlighting and fetching data. And in those cases, you can use cell references from your dataset.

      1. Conditional Formatting: Highlight Cells or Range > Go to Home > Conditional Formatting > New Rule > Select a Rule Type and Format > OK.


      Conditional Formatting

      2. VLOOKUP Function: Use TODAY()-30 in any cell, then use the VLOOKUP formula to fetch different entries. In that way, the dataset range remains intact.


      VLOOKUP Formula

      Hope, these ways help you to compensate the Excel Filter caveats. Feel free to comment if the solution doesn’t satisfy your seeking. Our Exceldemy Team is always there to help.

Leave a reply

Advanced Excel Exercises with Solutions PDF