Generate Recurring Activity Dates in Excel 365 by Frequency
Question details
Create a dynamic Excel 365 schedule that generates every occurrence of each activity based on its recurrence frequency and a selected start and end date.

- Product
- Microsoft Excel 365
- Device & OS
- not provided
- Scenario
- The user has an activity list with periodicity values such as daily, weekly, monthly, quarterly, semiannual, or annual and wants to expand it into dated schedule rows.
- Observed behavior
- The desired result is a sorted dynamic-array schedule that includes only occurrences within the selected date range.
Place the schedule start date in H2 and the end date in H3, and make sure H3 is on or after H2. Use consistent frequency labels in column D: daily, weekly, monthly, quarterly, semiannual, or annual.
Use a LET dynamic-array formula to expand all recurring activities
Use this method in Excel 365 when you want one spill formula to generate a complete, date-sorted schedule from an activity table.
Enter activity details in A5:D10, where column A is the activity name, columns B and C contain optional related details, and column D contains the recurrence frequency. The formula calculates each activity's dates and excludes dates after the selected end date.
In A5:D10, enter one activity per row. Put the activity name in column A, any category or owner details in columns B and C, and the frequency in column D.
Enter the first schedule date in H2 and the last schedule date in H3. Format both cells as Date.
Select an empty cell where the generated schedule should begin, then enter: =LET(s,$H$2,e,$H$3,t,A5:D10,a,CHOOSECOLS(t,1),b,CHOOSECOLS(t,2),c,CHOOSECOLS(t,3),f,LOWER(CHOOSECOLS(t,4)),SORT(DROP(REDUCE({"","","","",""},SEQUENCE(ROWS(t)),LAMBDA(x,r,LET(q,INDEX(f,r),d,SWITCH(q,"daily",SEQUENCE(e-s+1,,s),"weekly",SEQUENCE(QUOTIENT(e-s,7)+1,,s,7),"monthly",EDATE(s,SEQUENCE(QUOTIENT(DATEDIF(s,e,"m"),1)+1,,0,1)),"quarterly",EDATE(s,SEQUENCE(QUOTIENT(DATEDIF(s,e,"m"),3)+1,,0,3)),"semiannual",EDATE(s,SEQUENCE(QUOTIENT(DATEDIF(s,e,"m"),6)+1,,0,6)),"annual",EDATE(s,SEQUENCE(QUOTIENT(DATEDIF(s,e,"m"),12)+1,,0,12))),v,FILTER(d,d<=e),VSTACK(x,HSTACK(IF(v<>"",INDEX(a,r),""),IF(v<>"",INDEX(b,r),""),IF(v<>"",INDEX(c,r),""),IF(v<>"",INDEX(f,r),""),v))))),1),5,1))
Format the fifth column of the spilled result as a date by selecting the output dates and choosing Home > Number Format > Short Date or Long Date.

Create helper columns for each activity when a single spill formula is not suitable
Use helper columns when you need to review recurrence calculations row by row or your workbook requires a simpler, easier-to-audit layout.
Build a Recurring Activity Schedule with WPS Spreadsheet
WPS Spreadsheet provides a practical way to organize activity frequencies, calculate recurring dates, and maintain a date-sorted schedule. Create your activity table, use date formulas for each recurrence interval, and filter or sort the results as your schedule changes.
- 1. Create an activity table: Open WPS Spreadsheet and enter columns for Activity, Details, Frequency, Start Date, and Next Occurrence.
- 2. Add recurrence formulas: Use formulas such as =StartDate+1 for daily, =StartDate+7 for weekly, and =EDATE(StartDate,3) for quarterly recurrence dates.
- 3. Fill formulas for activities: Select the formula cell and drag the fill handle down to calculate dates for each activity row.
- 4. Sort the schedule by date: Select the completed table, then choose Data > Sort and sort the Next Occurrence column from earliest to latest.

Frequently Asked Questions
Why does the dynamic schedule formula return an error for one activity?
Check the frequency text in column D. It must match one of the supported labels exactly after lowercase conversion: daily, weekly, monthly, quarterly, semiannual, or annual.
Can the recurring schedule include activities that start on different dates?
Yes. Replace the single start-date reference in the formula with a Start Date column from the activity table, then use the date from the current row when generating that activity's occurrence dates.
How are monthly dates handled when the start date is near the end of a month?
The EDATE function advances by whole calendar months. For example, a recurrence beginning on January 31 can return the last valid day of shorter months, such as February 28 or February 29.
Can I add a biweekly recurrence option?
Yes. Add a "biweekly" entry to the SWITCH section and use SEQUENCE(QUOTIENT(end_date-start_date,14)+1,,start_date,14) to generate dates every 14 days.
Why is the formula not available in an older Excel version?
This approach uses Excel 365 dynamic-array functions, including LET, HSTACK, VSTACK, FILTER, and REDUCE. Older perpetual Excel versions may not include these functions.




