WPS Spreadsheet: Create a Calendar Sequence with Dates, Weekdays, and Week Numbers
Question details
Create a dynamic calendar sequence that displays dates, abbreviated weekdays, month labels, and week numbers in a structured layout.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user wants to generate a calendar for a selected starting date and number of days without entering each date and label manually.
- Observed behavior
- The desired calendar output should spill into a structured array with week numbers, month names, weekdays, and day numbers.
Confirm that your WPS Spreadsheet version supports dynamic-array functions such as LET, SEQUENCE, and VSTACK. Decide the calendar start date and the number of dates to generate; use 366 for a leap-year-length calendar.
Generate the calendar with a dynamic-array formula in WPS Spreadsheet
Use this solution when your WPS Spreadsheet version supports LET, SEQUENCE, TEXT, WEEKNUM, and VSTACK functions.
The formula creates a horizontal date sequence, formats each date into an abbreviated weekday, adds the month and week number on Saturdays, and stacks the results into four calendar rows. Change the start date and sequence length to fit your calendar period.
Open the worksheet in WPS Spreadsheet and select the cell where the top-left corner of the calendar should begin.
Enter the following formula in the selected cell and press Enter: =LET(start,DATE(2024,1,1),dates,SEQUENCE(,366,start),day_of_week,TEXT(dates,"ddd"),month_name,IF(day_of_week="Sat",TEXT(dates,"mmm"),""),week_num,IF(day_of_week="Sat","Week "&WEEKNUM(dates),""),day_num,DAY(dates),VSTACK(week_num,month_name,day_of_week,day_num))
In the formula, replace DATE(2024,1,1) with the required starting date and replace 366 in SEQUENCE(,366,start) with the number of dates to display.
Select the spilled calendar range and use the Home tab to adjust column width, alignment, borders, and font formatting for easier reading.
Build Dynamic Calendar Sequences Directly in WPS Spreadsheet
WPS Spreadsheet can generate the calendar with a single dynamic-array formula. Enter the formula in one cell, adjust the start date and sequence length, and format the automatically spilled results without manually filling every date.
- 1. Open WPS Spreadsheet: Launch WPS Office, open a workbook, and select the worksheet for the calendar.
- 2. Enter the formula: Select the calendar's starting cell and enter the LET, SEQUENCE, and VSTACK formula to generate the calendar array.
- 3. Customize the date range: Change the DATE value for the starting day and the SEQUENCE count for the required calendar length.
- 4. Format the result: Use the Home tab to apply borders, alignment, column widths, and other formatting to the spilled calendar.
Frequently Asked Questions
How do I change the first date in the WPS calendar sequence?
Edit the DATE(2024,1,1) portion of the formula in the output cell. For example, use DATE(2025,1,1) to start the calendar on January 1, 2025.
How can I generate fewer than 366 dates?
Change the 366 value in SEQUENCE(,366,start) to the required number of dates, such as 31 for one month or 365 for a standard year.
Why do the month and week labels appear only on Saturdays?
The formula uses IF(day_of_week="Sat",...) so month abbreviations and week numbers are shown on Saturdays. Replace "Sat" with another abbreviated weekday if you want the labels on a different day.
What should I do if the formula does not spill into multiple cells?
Check that the cells to the right and below the formula cell are empty and that your WPS Spreadsheet version supports dynamic-array functions, including SEQUENCE and VSTACK.
