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

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.

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.

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.

🔺 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.

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.

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.

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
.AutoFilter Field:=1, Criteria1:=">=" & Date - N, Operator:=xlAnd, Criteria2:="<=" & Date
End With
End Sub

In the Macro the 1^st macro holds the FilterToLastNDays function to filter the last 30 days. Then the 2^nd 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.

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.

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.

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.

🔺 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.

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.

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)

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

Step 3: Excel loads the data. Click on the Filter Icon of the Order Date Column. Click on Date/Time Filters > Between.

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.

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

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

Download Excel Workbook

Conclusion

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