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

Get FREE Advanced Excel Exercises with Solutions!

Typical Excel worksheets contain hundreds of rows with dates, amounts, etc. as entries. As a result, finding entries within a specific time period such as Excel date filter last 30 days is common among users.

Let’s say we have a dataset containing dates along with other entries. We want to filter the dates of the last 30 days existing within the worksheet.

Dataset-Excel Date Filter Last 30 Days

In this article, we use the Filter feature, VBA Macro, TODAY function, the FILTER function as well as the Power Query to Excel date filter last 30 days.

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

There are multiple ways to date filter the last 30 days. Follow the below section to find the most suitable one.

Method 1: Filter Last 30 Days Using Filter Feature

Excel offers the Filter feature to filter data while maintaining specific criteria. To date-filter a certain number of days, users can use Filter’s option-Between.

Step 1: Place your cursor in any cell then go to the Home tab > Editing section > Click on Sort & Filter > Click on Filter.

Filter Option-between-Excel Date Filter Last 30 Days

Step 2: Excel enables the filter feature for the columns. Click on the Filter Icon > Select Date Filters option > Click on the option Between.


Step 3: Excel takes a moment and then brings up the Custom AutoFilter window. Choose your desired options using the drop-down icons under Show rows where such as is before or equal to and is after or equal to. Then select respective dates using the Calendar Icons on the right side of the window. At last, click OK.

AutoFilter window

🔺 Afterward, Excel filters the worksheet’s entries maintaining a window of the last 30 days.


Also, you can choose to filter the entries for the last 60, 90, or what days you prefer.

🔄 Alternative to the Home tab’s filter feature, you can apply the filter feature using the Data tab. Go through Data > Sort & Filter section > Click on Filter.

Alternative Filter option

Method 2: Excel VBA to Filter Last 30 Days

VBA Macro allows users to write their own functions as we have done here with the FilterToLastNDays sub-procedure, now any user can use this function to filter dates of any number of last days. As mentioned earlier we have a dataset that has dates in B Column as shown in the below image.

VBA-Excel Date Filter Last 30 Days

Step 1: Before inserting the macro code, press ALT+F11 to open the Microsoft Visual Basic window. Alternatively, you can go to the Developer tab > Visual Macro to open Microsoft Visual Basic. In the Visual Basic window, insert a Module by clicking on Insert > Module.

Module Insertion

Step 2: Paste the following macro in the Module.

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 Macro the 1st macro holds the FilterToLastNDays function to filter the last 30 days. Then the 2nd macro defines the assigned function. VBA WITH statement provides the worksheet and ranges for filtering. At last, the AutoFilter command assigns the necessary criteria to execute the filtering.

Step 3: Hit F5 to run the macro after that return to the worksheet. You see Excel filters the entries maintaining the given criteria.

VBA Outcome-Excel Date Filter Last 30 Days

Also, you can just edit the day’s number (i.e., 30) after the FilterToLastNDays function to filter any last days.

Method 3: Using AND and TODAY Functions to Filter Last 30 Days

The AND function takes multiple logic in its argument. So, inserting The TODAY function in its argument to assign dates as criteria.

Step 1: Add a helper column adjacent to the dataset. Then 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.

Step 2: Press ENTER then drag the Fill Handle to display all the logic outcomes in TRUE or FALSE.

Helper Column

Step 3: Apply the Filter feature following Step 1 of Method 1. Afterward, click on the Filter Icon > Mark only the TRUE option > Click on OK.

Filter Options

🔺 Clicking OK, Excel displays only the entries expanding the last 30 days.


Method 4: Filtering Last Assigned Days Using FILTER Function (Excel 365)

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

FILTER (array, include, [if_empty])

Step 1: Type the below formula in the F4 cell.


FILTER Function-Excel Date Filter Last 30 Days

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

Step 2: Use the ENTER key to fetch all the entries that fall in that category.

FILTER Function Outcome

Method 5: Using Excel Power Query to Filter 30 Days

Power Query is an efficient tool to work with data. In-built Power Query offers the Filter Feature.

Step 1: Highlight the range then move to Data > Click on From Table/Range (in the Get & Transform Data)

Power Query-Excel Date Filter Last 30 Days

Step 2: Excel fetches a Create Table window. Excel automatically takes the range. Tick the My table has headers option. Then click on OK.

Create Table

Step 3: 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

Step 4: Excel takes a moment after that and brings the Filter Rows dialog box. Choose desired AND logic then provide dates expanding 30 or desired days as shown in the below image. Click on OK.

Filter Rows

Step 5: Excel loads the filtered data. Click the Close & Load feature > then select Close & Load.

Close & Load

🔺 At last, the filtered data get loaded into a new Worksheet as depicted in the below picture.

Power Query Outcome

Download Excel Workbook


In this article, we use the Filter Feature, VBA Macro, AND & TODAY, FILTER functions as well as Power Query to Excel date filter last 30 days. Use any of the ways to achieve the desired outcome. Hope these ways help you to achieve what you prefer. Comment, if you have any further inquiries or have anything to add.

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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