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.

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.


Download Excel Workbook


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

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

between

Step 3: Excel takes a moment 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.

Outcome

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

Read More: How to Use Custom Date Filter in Excel (5 Easy Ways)


Method 2: Excel VBA to Filter Last 30 Days

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

Macro

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

Read More: Excel VBA: Filter Date Range Based on Cell Value (Macro and UserForm)


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.

=AND(B5>=(TODAY()-30),B5<TODAY())

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 last 30 days.

Outcomes

Read More: Excel VBA: Filter Date before Today (With Quick Steps)


Similar Readings


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

The FILTER function is only available in 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(B4:D16,B4:B16>=TODAY()-30,B4:B16<TODAY())

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 fell in that category.

FILTER Function Outcome


Method 5: Using Excel Power Query to Filter 30 Days

Power Query is any 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 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

Read More: How to Filter Date Range in Excel (5 Easy Methods)


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.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo