Excel Filter Dates by Week: Quick Methods

Formulas & functions · Updated November 19, 2026

To filter dates for the current or a nearby week in Excel for Microsoft 365 on Windows, select a cell in your data, choose Data > Filter, open the date column’s dropdown, and select Date Filters > This Week, Last Week, or another available preset. For a week with exact boundaries, use Date Filters > Between or add a helper column that calculates each date’s week-start date.

Use Excel’s built-in weekly date filters

Excel’s AutoFilter includes relative date options such as This Week, Last Week, Next Week, and Next Month. They are useful when you want to review a changing period without typing dates each time. The filter updates in relation to the current date, so a This Week filter will represent a different date range next week.

  1. Make sure your data has a header row and that the dates are in one column.
  2. Click a cell in the data range, then choose Data > Filter. On Windows, Ctrl+Shift+L also toggles filtering on or off.
  3. Open the dropdown in the date column’s header.
  4. Choose Date Filters, then select This Week, Last Week, or the period you need.

Excel hides rows that do not match the selected period; it does not delete them. To show all records again, open the same dropdown and choose Clear Filter From [column name], or use Data > Clear to clear filters across the range. If Date Filters is unavailable, or the menu offers only text filters, Excel may be reading the entries as text rather than dates.

What Excel means by a week

A week can mean Monday through Sunday, Sunday through Saturday, or a seven-day period chosen by a business. The built-in weekly presets are relative to today, but their calendar boundaries may be affected by regional settings. If the exact first day matters—for example, because a report must always run Monday to Sunday—use explicit start and end dates rather than relying on a preset. This also makes the chosen reporting rule easier for another person to understand.

Be clear about whether the week is a calendar week or a rolling seven-day period. “This week” normally means the current calendar week, not the last seven days. A rolling period ending today is a different request: its start date changes daily, and it may cross a month or year boundary. Choosing the right definition before filtering prevents two people from producing different totals from the same data.

Another distinction is the week-numbering system. ISO weeks begin on Monday, and ISO week 1 is the week containing the year’s first Thursday. As a result, early January dates can belong to the final ISO week of the previous year, and late December dates can belong to week 1 of the next ISO year. A week number by itself is not a unique identifier: week 12 occurs every year. For a stable label or filter, include the year or use the actual week-start date.

Filter an exact date range

For a particular week, open the date dropdown and choose Date Filters > Between. Enter the week’s start and end dates in the custom filter dialog. For a Monday-to-Sunday week, for instance, a report for November 2 through November 8, 2026, uses those dates as its inclusive boundaries. This is suitable when the column contains dates without times and you need a one-off filter.

Dates with times need more care. Excel stores a time as a fraction of a day, so a value such as November 8, 2026 at 3:00 p.m. is later than November 8 at midnight. A filter whose upper limit is midnight at the start of November 8 could omit that afternoon record. To avoid ambiguity, use a helper test that includes the start of the week and excludes the start of the following week.

With date-time values in A2:A100 and the Monday start date in B1, enter this formula in C2 and fill it down:

=AND(A2>=$B$1,A2<$B$1+7)

If B1 contains November 2, 2026, a timestamp on Sunday, November 8 at 11:00 p.m. returns TRUE, while a timestamp at midnight on Monday, November 9 returns FALSE. Filter column C to TRUE. The exclusive upper boundary includes every time on the final day without accidentally including the next week. Make sure B1 contains a real Excel date, not a text string that merely looks like one.

Group records by week with a helper column

A helper column is useful when you repeat the same weekly analysis, want a clear week label beside each record, or need to choose weeks from a filter list. It turns each date into the Monday that starts its week. With dates or timestamps in A2:A100, enter the following in B2 and fill down:

=IF(A2="","",INT(A2)-WEEKDAY(INT(A2),2)+1)

For a date of Wednesday, November 4, 2026, the result is Monday, November 2, 2026. The INT function removes any time component; WEEKDAY with return type 2 numbers Monday as 1 and Sunday as 7. Format the helper cells as dates if Excel displays serial numbers, then filter the helper column for the Monday you want. The blank check keeps empty source cells from appearing as dates in the helper results.

For a Sunday-start week, this formula is not the right convention: the formula above intentionally groups Monday through Sunday. Decide on the convention first and use it consistently in reports, labels, and summaries. If records come from more than one time zone, also confirm that their stored dates and times have been converted to the time zone used for reporting; otherwise, an event near midnight may fall into a different calendar day or week.

Check that the date column contains real dates

Excel dates are stored as numbers, even when the cells are formatted to display a readable date. Imported data can instead contain text such as “11/04/2026.” Text may look identical to a date on screen but will not always sort, group, or filter as expected. A useful first check is to select a cell and inspect the formula bar and number format. You can also try changing the cell’s number format; a genuine date usually changes its display, while text usually does not.

To correct text dates, first make a copy of the source data. If the entries follow a consistent format, select the affected column and use Data > Text to Columns, then follow the wizard to convert them to dates and choose the correct order, such as MDY or DMY. Choosing the wrong order can silently misinterpret dates where both the day and month are 12 or less. For data from external systems, importing with an explicit locale or date format is safer than relying on automatic interpretation.

  • Look for blanks, text values, and errors mixed into the date column; one inconsistent entry can make filtering confusing.
  • Use a single date format for readability, but remember that formatting alone does not convert text into dates.
  • After converting values, reapply the filter and check a few known records at the week boundaries.

Apply filters reliably to a growing data set

Filters work best when each column has one header and each row represents one record. Avoid blank rows or merged cells inside the data range, since they can cause Excel to identify only part of the list. If the data will grow, convert the range to an Excel table with Insert > Table and confirm that the table has headers. Tables provide filter dropdowns in their header row and generally expand when new records are added directly below them.

When using a helper column in a table, enter its formula in the first data row; Excel normally fills the calculated column through the table. If you paste new rows in a way that does not extend the table, the new records may not be included in the filter. Check the table boundary and confirm the helper formula is present for the added rows. A weekly filter can only work on records that are actually inside the filtered range.

Filtering is also different from sorting or grouping. Filtering hides nonmatching rows but leaves the source data in place. Sorting changes row order, and grouping dates in a PivotTable changes how summarized items are displayed. If you are using a PivotTable, its date grouping and refresh behavior are separate from the ordinary worksheet AutoFilter.

Common problems and practical fixes

If the expected weekly preset does not appear, start by confirming Excel recognizes the column as dates. Check that filtering is turned on for the full range and that the header is not blank. If the dropdown shows individual dates in an unexpected hierarchy, expand the relevant year or month entries, or choose a Date Filters command instead of selecting dates one by one.

If a date on Sunday appears in the wrong week, the issue may be the week-start convention rather than a bad filter. Compare the result with the reporting rule your team uses. A helper column based on a Monday start provides a predictable rule; a different organization may need a Sunday start. Also check whether the cells contain hidden times, especially when a boundary date seems to be partly missing.

If the filter seems not to update after new records are added, check whether the new rows are inside the filtered range or Excel table. Clear the filter and apply it again if necessary. If a file is shared, remember that each user may have a different filter view or may change the shared worksheet’s visible rows; coordinate before treating a filtered view as a changed or reduced data set.

Choose between presets, date ranges, and helper columns

Use the built-in This Week or Last Week preset for a quick, current review. Choose Between for a one-time period with known dates. Use a helper column when the week definition must be explicit, when timestamps are present, or when you need to filter repeatedly by week-start date. These approaches can be combined: keep the original date column for detail and filter the helper column for the reporting period.

For recurring summaries rather than simply hiding rows, a PivotTable can group date records into weeks or summarize values by a helper week-start field. A PivotTable is useful when the question is “How many orders occurred each week?” rather than “Show me the individual rows from this week.” If exact week rules are important, a helper field is often easier to audit than relying on an automatic grouping choice. Refresh the PivotTable after changing or adding source data.

Frequently Asked Questions

Can Excel filter dates for the current week automatically?
Yes. Open the date column’s filter dropdown and choose Date Filters > This Week. It is a relative preset, so the matching dates change as the current week changes. Use explicit boundaries if your reporting week has a special start day.

How do I filter dates from Monday to Sunday?
Use a helper column that returns the Monday starting each record’s week, then filter that column for the Monday you need. The formula in this guide uses Monday as the first day and removes any time portion from the source value.

Can I filter the last seven days instead of last week?
Those periods are not the same. Last Week refers to a calendar week before the current one, while the last seven days is a rolling range. For the rolling range, set explicit start and end dates or use a helper test with those boundaries.

Why does Excel treat my dates like text?
The values may have been imported as text, or their day/month order may not match the settings Excel used to interpret them. Convert them with a method that specifies the correct date order, then verify the results before relying on the filter.

Will filtering delete rows that do not match?
No. AutoFilter hides rows that fail the condition; it does not remove their contents. Clear the filter to show them again. Be cautious when copying or deleting visible rows, because those actions can affect a filtered selection differently from simply viewing it.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.