WPS Spreadsheets: Repeat Each Date Three Times Automatically
Question details
The user needs to create a monthly date sequence in which every date appears in three consecutive rows automatically.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A date list must include each calendar date, including weekends, three times without manually entering or dragging every repeated value.
- Observed behavior
- The user needs a formula that spills or fills each date into three consecutive rows while preserving date values.
Enter the first date in one cell and the total number of dates in another cell. Format the output range as Date before checking the repeated results.
Use a dynamic-array formula to repeat every date three times
Use this method in WPS Spreadsheets versions that support SEQUENCE, MMULT, and TOCOL; the formula spills the complete repeated date list automatically.
Place the starting date in A1 and the number of dates to generate in B1. The formula creates one date per calendar day and repeats each date three times, including Saturdays and Sundays.
Do not append text such as "x3" to the formula result, because that converts the dates into text values rather than usable dates.
In cell A1, enter the first date, such as 1/1/2023.
In cell B1, enter the number of dates required, such as 31.
Select an empty output cell, such as C1, and enter =TOCOL(MMULT(SEQUENCE(B1,,A1),SEQUENCE(,3,1,0))). Press Enter to spill the repeated dates downward.
Select the spilled output range, open the Home tab, choose the Number Format menu, and select Date.
Fill down a compatible formula for older spreadsheet versions
Use this fallback when dynamic-array functions are unavailable or when you want to control the output range manually.
Create a Three-Row Date Sequence in WPS Spreadsheets
WPS Spreadsheets can generate and repeat a complete date sequence with a dynamic-array formula, or use a simple fill-down formula when dynamic arrays are unavailable. The result remains suitable for sorting, filtering, and date-based calculations.
- 1. Open WPS Spreadsheets: Launch WPS Office and choose Spreadsheet to create or open the workbook.
- 2. Set the inputs: Enter the first date in A1 and the required number of dates in B1.
- 3. Generate the repeated list: In an empty cell, enter =TOCOL(MMULT(SEQUENCE(B1,,A1),SEQUENCE(,3,1,0))) and press Enter.
- 4. Format and review: Select the output cells, use Home > Number Format > Date, and verify that every date appears three times.
Frequently Asked Questions
How do I repeat each date exactly three times in WPS Spreadsheets?
Enter the first date in A1, the number of dates in B1, and use =TOCOL(MMULT(SEQUENCE(B1,,A1),SEQUENCE(,3,1,0))) in an empty cell. The formula spills each date into three consecutive rows.
Will the formula repeat weekends too?
Yes. The formula advances one calendar day at a time, so Saturday and Sunday are included and repeated three times like every other date.
Why are my repeated dates displayed as numbers?
Spreadsheet applications store dates as serial numbers. Select the results, open Home > Number Format, and choose Date to display them as calendar dates.
What can I use if TOCOL or SEQUENCE is not supported?
Enter =$A$1+INT((ROWS($C$1:C1)-1)/3) in C1 and fill it down for three times the required number of dates. Format column C as Date.
Can I add text such as "x3" after each date?
Adding text with an ampersand, such as =date&" x3", converts the result to text. Keep the output as a date if you need sorting, filtering, or date calculations.
