How to filter all date cells before or after today in Excel?
In this article, we will explore step-by-step methods for filtering all date cells that are before or after today in an Excel worksheet. Managing and analyzing time-sensitive data is a frequent requirement, especially when working with logs, schedules, project timelines, or tracking due dates. Efficiently filtering by dates makes it easier to focus only on records that matter at a given time, thereby improving workflow and decision-making accuracy.
➤ Filter all date cells before or after today with Filter function
➤ Filter all date cells before or after today with VBA code
➤ Filter all date cells before or after today with an Excel formula helper column
Filter all date cells before or after today with Filter function
Excel's built-in Filter feature offers a straightforward way to identify and display dates either before or after today. This approach is visual and does not require any formulas or VBA code, making it ideal for users who want a quick way to filter current datasets without using formulas or VBA.
1. Select the column containing your dates, then go to the Data tab and click Filter on the Ribbon. You should see filter arrows appear next to each column header, as shown below:
2. Click the drop-down arrow in the date column header. Hover over Date Filters, then choose either Before or After depending on your needs. For example, select Before to find dates earlier than today.
3. In the Custom AutoFilter dialog box, click the Date Picker 
icon to open the calendar. Click the Today button to quickly set today’s date.
4. Click OK. Excel will filter your data to show only rows where the date meets your specified condition (before or after today), as shown below:
This method is efficient for moderate-sized tables and very accessible, as no advanced skills are required. However, you’ll need to repeat the process manually if your data updates or the criteria change.
⚠️ Note: If your date column contains text or is not formatted as proper Excel dates, filter options under Date Filters may not appear.
Filter all date cells before or after today with VBA code
If you need to repeatedly filter data or want to automate the process for large datasets, using VBA code is a highly practical approach. It is particularly useful for analysts, report automation, or any workflow where filtering by today's date recurs regularly. With VBA, you can apply the filter in one click, avoiding repetitive manual filtering. Please note that using VBA will require saving your workbook with macro support (.xlsm format), and some users may have restricted access due to macro security settings.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the editor, click Insert > Module, then paste one of the following code blocks into the module window.
VBA code: Filter all date cells before today:
Sub FilterDateBeforeToday()
Dim xLastRow As Long
Dim xRg As Range
On Error Resume Next
Set xRg = Application.InputBox("Please select filtered column：", "KuTools for Excel", Selection.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
If xRg.Count = 1 Then Set xRg = xRg.CurrentRegion
xRg.AutoFilter 1, "<" & CDbl(Date)
Application.ScreenUpdating = True
End Sub
VBA code: Filter all date cells after today:
Sub FilterDateAfterToday()
Dim xLastRow As Long
Dim xRg As Range
On Error Resume Next
Set xRg = Application.InputBox("Please select filtered column：", "KuTools for Excel", Selection.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
If xRg.Count = 1 Then Set xRg = xRg.CurrentRegion
xRg.AutoFilter 1, ">" & CDbl(Date)
Application.ScreenUpdating = True
End Sub
3. After copying and pasting one of the above codes, press the F5 key to run it. A prompt box will appear, allowing you to select the range or column containing your dates. Be sure to select only the relevant data range, and ensure your dates are in proper Excel date format to avoid code errors.
4. Click OK to execute the filter. The rows where the date is before or after today (depending on your code choice) will be automatically displayed, with all other rows hidden.
⚠️ Note: VBA-based filtering minimizes user interaction and is suitable for both simple and advanced needs. However, always save your data before applying VBA and double-check your selected range to avoid filtering out essential rows inadvertently. If you encounter errors, ensure all date values are valid dates—non-date entries can cause the macro to malfunction. For continued convenience, consider assigning the macro to a button for one-click filtering.
Filter all date cells before or after today with an Excel formula helper column
Another versatile solution involves adding a helper column with an Excel formula to flag dates that are before or after today. This method offers great flexibility in defining and adjusting your filtering conditions, making it especially suitable for dynamic tables, complex filtering, or when you want to perform further analysis with flagged data. Unlike in-place filtering, the helper column approach lets you visually mark rows and then apply Excel’s Filter tool for more custom control. This technique is easy to implement and excellent for tracking deadlines, overdue tasks, or monitoring upcoming events.
1. Insert a new column next to your date column. For illustration, suppose your dates are in column A, starting from cell A2. In the new helper column (suppose column B), enter the following formula in B2 to flag dates before today:
=A2<TODAY()The formula will return TRUE for dates before today and FALSE otherwise.
If you want to flag dates after today (i.e., future dates), use this version in B2:
=A2>TODAY()2. Drag the formula down the helper column to evaluate all rows in your dataset. If the data starts from row 2, simply select B2, move your cursor to the bottom-right corner until it becomes a small black plus sign, and double-click to auto-fill for all rows.
3. Now, with your helper column showing TRUE or FALSE against each row, select the header of the new helper column and enable Filter from the Data tab (if it is not already enabled). Click the drop-down arrow and filter by TRUE to view only the rows matching your condition (either before or after today, depending on the formula used).
💡 Tips: To avoid confusion, consider labeling your helper column according to the filter logic, such as "Before_Today" or "After_Today." If you need to filter by specific conditions (for example, only dates within a moving window), adjust the formula as needed. Always check that your date column contains only valid Excel dates, as blank or non-date entries may affect the TRUE/FALSE results.
ℹ️ Additional Notes:
• This formula-based method is particularly helpful for viewing, sorting, or further analyzing data without losing track of the original information. It also supports flexible conditional formatting and summary calculations based on the TRUE/FALSE values, though it does require an extra column. The transparency and reusability of this approach often outweigh that small inconvenience.
• If your table is frequently updated, simply reapply or extend the formula as new rows are added. Should sorting or filtering not work as expected, double-check for blank or inconsistent entries in your date column, and recalculate the helper formulas as necessary (Formulas > Calculate Now if calculation is set to manual).
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in