Function Problems

How to Filter Excel Rows by Today's Date Using TODAY Function or Power Query

John Wilson Oct 14, 2026 869 views

Question details

The user needs to dynamically filter rows of data containing event dates into separate worksheets for past and future events based on the current date.

How to Filter Excel Rows by Today's Date

Product: Excel
Device & OS: not provided
Scenario: Organizing event dates collected from Microsoft Forms into categorized worksheets to easily separate past events from future ones.
Observed behavior: A dynamic method is required to separate and display rows depending on whether the event date is before, equal to, or after today's date.

Before you start

Make sure that the cells containing your event dates are formatted as actual Date values, not as plain text, otherwise the date comparison formulas will fail to recognize them correctly.

Solution 1Recommended

Use the FILTER and TODAY Functions for Dynamic Sorting

This is the quickest and most dynamic way to filter rows based on the current date across different worksheets using built-in array formulas.

The FILTER function allows you to extract records that meet specific criteria dynamically. By pairing it with the TODAY() function, your filtered lists will automatically update every day.

Prepare your destination sheet

Open the worksheet where you want the future events to be displayed (e.g., a sheet named 'Future Events').

Enter the FILTER formula for future events

In cell A2, enter the formula =FILTER(Sheet1!A2:E6, Sheet1!D2:D6>=TODAY()). Adjust the ranges so that 'Sheet1!A2:E6' is your source data and 'Sheet1!D2:D6' is your event date column.

Filter for past events

Open your 'Past Events' worksheet and enter =FILTER(Sheet1!A2:E6, Sheet1!D2:D6<TODAY()) in cell A2 to filter out events that have already occurred.

Use the FILTER and TODAY Functions for Dynamic Sorting

Dynamic Arrays: Ensure you have enough empty space below and to the right of your formula, as the FILTER function will spill the results into adjacent cells automatically.

Solution 2

Use Power Query for Advanced Data Filtering

Ideal for handling large datasets imported from Microsoft Forms or external sources, providing a robust ETL (Extract, Transform, Load) approach.

Process Data Efficiently with WPS Spreadsheet

Dynamically Filter Dates Using WPS Spreadsheet

WPS Spreadsheet is a powerful and free tool that fully supports advanced array functions like FILTER and TODAY, allowing you to seamlessly manage and analyze your event dates without complex configurations.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your Microsoft Forms event data.
2. Navigate to the target sheet: Create or open the destination sheet where you want to output the filtered data.
3. Apply the FILTER formula: Type =FILTER(Sheet1!A2:E6, Sheet1!D2:D6>=TODAY()) in the top-left cell of your destination range to instantly list all future events.
4. Create the past events view: In a separate sheet, apply =FILTER(Sheet1!A2:E6, Sheet1!D2:D6<TODAY()) to dynamically extract past events.

Fully compatible with Microsoft Excel formulas, .xlsx files, and data structures.Native support for dynamic array functions like FILTER to instantly spill results.Lightweight, fast, and completely free to use for everyday data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my FILTER function return a #CALC! error?

This error usually happens when there are no records that match your criteria (e.g., there are no future events in your dataset). You can prevent this by adding a third argument to your formula to display a custom message, such as: =FILTER(A2:E6, D2:D6>=TODAY(), "No events found").

How do I filter for events happening exactly today?

You can easily modify the criteria in your FILTER formula to use the equals operator. For example, use =FILTER(Sheet1!A2:E6, Sheet1!D2:D6=TODAY()) to only extract rows where the date matches the current day exactly.

Why aren't my dates being recognized by the TODAY function?

Your dates might be stored as text strings rather than actual date serial numbers. To fix this, select your date column, go to the Data tab on the ribbon, click 'Text to Columns', and immediately click 'Finish' to convert the text into proper date formats.

Can I filter data by date without using complex formulas?

Yes, you can use the built-in AutoFilter feature. Highlight your data headers, go to the Data tab, and click 'Filter'. Click the drop-down arrow on your date column, navigate to 'Date Filters', and choose dynamic options like 'Today', 'Tomorrow', 'Before...', or 'After...'.

Excel & Data Problems