Create a Monthly Task Schedule in Excel 2019 From Due Dates
Question details
Create a monthly schedule that lists tasks, due dates, and related events from a source list based on the selected month.
- Product
- Microsoft Excel 2019
- Device & OS
- not provided
- Scenario
- The user has multiple tasks and events with due dates and needs a schedule that returns all matching records for a specific month.
- Observed behavior
- Excel 2019 does not provide the FILTER, SORT, or dynamic array functions needed for a single spill formula.
Arrange the source data in one continuous range with headers, such as Task in column A, Event in column B, and Due Date in column C. Confirm that every due date is stored as a real Excel date rather than text.
Use a month helper column with INDEX and AGGREGATE formulas
Use this method when you need a formula-based monthly schedule that remains compatible with Excel 2019.
A helper column identifies the month for every due date. INDEX and AGGREGATE then return the first, second, and subsequent records matching the month selected in the schedule without requiring dynamic arrays or Ctrl+Shift+Enter array entry.
In an empty helper column, such as E2, enter =TEXT(C2,"yyyy-mm") when due dates are in column C. Fill the formula down through the task list.
In the monthly schedule, enter any date from the required month in B1, for example 1/1/2025. Format B1 as mmmm yyyy if you want it to display as a month name.
In the first Task result cell, such as A5, enter =IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,(ROW($A$2:$A$100)-ROW($A$2)+1)/($E$2:$E$100=TEXT($B$1,"yyyy-mm")),ROWS(A$5:A5))),"") and fill down.
In B5 for Event, use =IFERROR(INDEX($B$2:$B$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/($E$2:$E$100=TEXT($B$1,"yyyy-mm")),ROWS(B$5:B5))),""). In C5 for Due Date, use the same formula with $C$2:$C$100 as the INDEX range, then fill both formulas down.
Create a monthly schedule with Power Query
Use Power Query when the source list is large or imported regularly and you prefer a refreshable query instead of worksheet formulas.
Build a Refreshable Monthly Task Schedule in WPS Spreadsheets
WPS Spreadsheets can organize task, event, and due-date data into a monthly schedule using a helper month column and lookup formulas. Set one month cell as the schedule control, copy the result formulas down, and update the displayed month whenever needed.
- 1. Add task list headers: In WPS Spreadsheets, create columns for Task, Event, and Due Date, then enter each task on a separate row.
- 2. Create the month helper column: Add a Month column and enter =TEXT(C2,"yyyy-mm") beside the first due date. Drag the fill handle down to apply it to all task rows.
- 3. Choose the schedule month: Enter a date for the target month in a schedule control cell, such as B1, and format it as mmmm yyyy from Home > Number Format.
- 4. Return matching records: Use INDEX with AGGREGATE in the schedule output area to return matching task, event, and due-date rows based on the month key.
Frequently Asked Questions
Why does FILTER not work in Excel 2019?
FILTER is a dynamic array function available in newer Microsoft 365 and supported newer Excel versions, but it is not included in Excel 2019. Use helper columns with INDEX and AGGREGATE, or use Power Query instead.
How do I make the schedule show a different month?
Enter any valid date within the desired month in the month control cell, such as B1. The formulas compare its TEXT(B1,"yyyy-mm") value with the helper month column.
Can the same task appear more than once in the monthly schedule?
Yes. Each source row is evaluated separately, so multiple task rows with the same task name or multiple events in the same month can all be returned.
Why is my due date missing from the monthly results?
Check that the Due Date cell is a real date value. Select the cell and apply Home > Number Format > Short Date; if it does not change correctly, convert the text value to a date before using the formulas.
