How to Auto-Fill Consecutive Dates and Display Them as Text in WPS Office Spreadsheets
Question details
The user wants to create a row of consecutive dates in WPS Office Spreadsheets and display each one as full text including weekday, day, month, and year.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is working with date cells in a spreadsheet and needs them to auto-fill day-by-day while showing a long text date format.
- Observed behavior
- When the date cells are copied, only the year changes because the entries are text or have an unsuitable custom format instead of being real date values.
Check that the cells contain real date values rather than text. Select the cells and set the number format to General temporarily; real dates appear as serial numbers, while text entries remain unchanged.
Format the Cells and Drag the Fill Handle to Create Consecutive Dates
Use this method when you want the fastest way to auto-fill a row of consecutive dates and display them as weekday, day, month, and year.
WPS Office Spreadsheets increments dates automatically only when the starting cell contains a valid date. Applying a custom format changes the display without affecting the underlying date value.
Select the row or range where the consecutive dates should appear, then press Ctrl+1 or right-click and choose Format Cells.
On the Number tab, select Custom, enter dddd d mmmm yyyy in the Type box, and click OK.
In the first cell, type a real date such as 16-Oct-2023 and press Enter.
Click the cell, hover over the fill handle at the bottom-right corner, and drag it across the row to fill consecutive dates.
Use the Fill Series Dialog When Dates Do Not Increment
Use this method if dragging the fill handle still does not increment the dates or if the cells are behaving like text.
Auto-Fill Consecutive Date Series in WPS Office Spreadsheets
Use WPS Office Spreadsheets to enter real date values, apply a dddd d mmmm yyyy custom format, and drag the fill handle or use Fill Series to generate consecutive dates as full text.
- 1. Download and install WPS Office: Go to the official WPS Office download page, install WPS Office, and open WPS Spreadsheets.
- 2. Enter and format the starting date: Type a valid date such as 16-Oct-2023 in a cell, then open Format Cells > Number > Custom and enter dddd d mmmm yyyy.
- 3. Drag or use Fill Series: Drag the fill handle across the row, or click Home > Fill > Series, choose Date and Day, and click OK to generate consecutive dates.
- 4. Verify real date values: If the date series does not increment, set the cell format to General; real dates appear as numbers while text remains unchanged, so re-enter the date if needed.
Frequently Asked Questions
Why does dragging a date in WPS Spreadsheets only change the year?
This usually means the cell contains text or a custom format that is not recognized as a real date. Re-enter the date as a valid date value and apply the dddd d mmmm yyyy format before dragging.
How do I display dates as weekday day month year text?
Select the date cells, press Ctrl+1 to open Format Cells, go to Number > Custom, and type dddd d mmmm yyyy in the Type box.
What date format code shows Monday 16 October 2023?
Use the custom number format code dddd d mmmm yyyy. WPS Spreadsheets displays the weekday, day, month, and year as text based on the cell's real date value.
Why does Fill Series not increment my dates?
Make sure the starting cell contains a real date, not text. In the Series dialog, select Date, choose Day as the Date unit, and set the Step value to 1.
Can I use this date fill method with Excel files opened in WPS Office?
Yes, WPS Office Spreadsheets supports Excel XLSX files and date formats, so you can apply the same custom format and date series methods.
