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.
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 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 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 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.
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.
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 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.
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
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 last 30 days.
- How to Filter by Date in Excel (4 Quick Methods)
- VBA to Pivot Table Filter Between Two Dates in Excel
- How to Filter Date Range in Pivot Table with Excel VBA
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.
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.
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)
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 Close & Load feature > then select Close & Load.
🔺 At last, the filtered data get loaded into a new Worksheet as depicted in the below picture.
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.