How to Show Due Dates from Multiple Columns for the Current Week in Excel
Question details
The user wants to build a dynamic report that returns IDs, descriptions, and specific due-date columns containing dates that fall within the current week.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a dynamic tracking report to monitor tasks or items due in the current week across a dataset with multiple date columns.
- Observed behavior
- A filtered dataset that automatically updates to display only the relevant identifier columns and the specific date columns matching current-week dates.
Ensure you are using a version of Excel that supports modern dynamic array functions (such as LET, FILTER, VSTACK, and HSTACK), like Microsoft 365 or Excel 2021.
Use a Dynamic Array Formula with LET and FILTER
Create a dynamic report using modern Excel functions to automatically filter rows and columns based on the current week.
This method uses a combination of advanced functions to evaluate each date column. It calculates the start and end of the current week, discards columns with no relevant dates, and then filters the rows so only items due this week are displayed.
Set up a LET function to determine the start (Monday) and end (Sunday) of the current week by referencing the TODAY() function.
Use BYCOL combined with the week boundaries to check which date columns contain at least one date falling in the current week, discarding the rest of the date columns.
Use BYROW on the remaining columns to identify and keep only the rows where at least one of the dates falls within the current week.
Use FILTER, HSTACK, and VSTACK to merge the base columns (IDs and descriptions) with the filtered date columns into a single dynamic array output.
Automate with Office Scripts
Use an Office Script to automate the filtering, formatting, and extraction of due dates if you prefer a programmed approach over complex formulas.
Filter Due Dates Dynamically in WPS Spreadsheet
WPS Spreadsheet provides robust support for advanced formulas and dynamic arrays, making it easy to track due dates, manage your schedules efficiently, and manipulate complex data.
- 1. Open your Workbook: Launch WPS Spreadsheet and open your task tracking file containing the multiple date columns.
- 2. Enter the Formula: Select the top-left cell of your destination range and input your dynamic array formula using FILTER and LET.
- 3. Format the Dates: Select the output date columns, right-click, choose 'Format Cells', and apply your preferred short-date format.
Frequently Asked Questions
Can I change the start day of the week in my dynamic formula?
Yes. By adjusting the return_type parameter in the WEEKDAY function inside your LET formula, you can set the week to start on Sunday or any other day instead of Monday.
What happens if no due dates fall within the current week?
If no dates match the criteria, functions like FILTER will return a #CALC! error. You can prevent this by using the [if_empty] argument in the FILTER function to return a custom text string like 'Nothing is due this week'.
Do these dynamic array formulas work in older versions of Excel?
No. Advanced functions like LET, VSTACK, HSTACK, BYROW, and BYCOL require Microsoft 365 or Excel 2021 and newer. For older versions, you will need to use alternative methods such as standard PivotTables or VBA macros.
