Function Problems

Generate Recurring Activity Dates in Excel 365 by Frequency

John WilsonJohn Wilson Nov 12, 2026 868 views

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.

Generate Recurring Activity Dates in Excel 365 by Frequency
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.
Before you start

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.

Solution 1Recommended

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.

1
Prepare the activity table

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.

2
Set the date range

Enter the first schedule date in H2 and the last schedule date in H3. Format both cells as Date.

3
Enter the recurring schedule formula

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))

4
Format the generated date column

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.

Use a LET dynamic-array formula to expand all recurring activities
Frequency labels must match: The formula recognizes daily, weekly, monthly, quarterly, semiannual, and annual. Correct spelling or label differences in column D if an activity does not produce dates.
Create recurring schedules in WPS Spreadsheet

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. 1. Create an activity table: Open WPS Spreadsheet and enter columns for Activity, Details, Frequency, Start Date, and Next Occurrence.
  2. 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. 3. Fill formulas for activities: Select the formula cell and drag the fill handle down to calculate dates for each activity row.
  4. 4. Sort the schedule by date: Select the completed table, then choose Data > Sort and sort the Next Occurrence column from earliest to latest.
Create daily, weekly, monthly, quarterly, semiannual, and annual activity schedules.Use spreadsheet formulas and date functions to calculate future occurrence dates.Open and edit Microsoft Excel .xlsx files with strong format compatibility.Use a lightweight, familiar spreadsheet interface for schedule maintenance.
microsoft office alternative - wps office

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.