WPS Spreadsheet: List Every Monday, Wednesday, and Friday in Excel
Question details
The user needs to generate every Monday, Wednesday, and Friday date in an entire year with one dynamic formula.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A yearly date list is required for recurring schedules that occur every Monday, Wednesday, and Friday.
- Observed behavior
- The desired dates are not yet generated automatically in a spilled worksheet list.
Use a WPS Spreadsheet version that supports dynamic array functions such as LET, SEQUENCE, and FILTER. Decide which year to use and make sure the destination cells below the formula are empty so the results can spill.
Generate all Monday, Wednesday, and Friday dates with one dynamic formula
Use this solution when your WPS Spreadsheet version supports dynamic array formulas and you want the complete date list to update from one year value.
The formula creates every date from January 1 through December 31, evaluates each date's weekday, and keeps Monday, Wednesday, and Friday. With WEEKDAY using return type 2, Monday is 1, Wednesday is 3, and Friday is 5.
Select an empty starting cell, such as A1, and paste: =LET(yr,2024,start,DATE(yr,1,1),days,DATE(yr,12,31)-start+1,dates,SEQUENCE(days,,start),weekday,WEEKDAY(dates,2),FILTER(dates,ISNUMBER(XMATCH(weekday,{1,3,5}))))
In the formula bar, replace 2024 in the yr variable with the required year, such as 2025, and press Enter.
Select the spilled date range, open the Home tab, choose the Number Format menu, and select Date or a custom date format.
Create recurring weekday date lists in WPS Spreadsheet
WPS Spreadsheet can generate a full year's Monday, Wednesday, and Friday dates with dynamic formulas. Enter the formula once, change the year variable, and format the spilled results as dates for schedules, calendars, or planning sheets.
- 1. Open a spreadsheet: Launch WPS Office, choose Spreadsheet, and open a blank workbook or an existing .xlsx file.
- 2. Select a starting cell: Click an empty cell where the yearly date list should begin, such as A1.
- 3. Enter the dynamic formula: Paste the LET, SEQUENCE, WEEKDAY, XMATCH, and FILTER formula into the formula bar, then press Enter.
- 4. Set the target year: Edit the yr value inside the formula to the year you need and press Enter to refresh the spilled dates.
Frequently Asked Questions
How do I change the year in the formula?
Edit the number after yr in the LET formula. For example, change yr,2024 to yr,2025, then press Enter to regenerate the dates.
Why are the results displayed as numbers instead of dates?
Select the spilled results, go to the Home tab, open Number Format, and choose Date. Spreadsheet applications store dates as serial numbers internally.
What do the weekday numbers 1, 3, and 5 represent?
Because WEEKDAY uses return type 2, Monday is 1, Tuesday is 2, Wednesday is 3, Thursday is 4, and Friday is 5.
Why does the formula show a spill or blocked-cell error?
Clear any content or merged cells in the area below and beside the formula's starting cell. Dynamic array results need an unobstructed range to spill.
