Generate Dates for Months with Different Numbers of Days in WPS Excel
Question details
The user needs to generate a month-specific date list using real Excel dates so that February and other shorter months stop at the correct final day instead of producing invalid dates.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user wants to create a date sequence for a selected month without manually incrementing day numbers or allowing invalid dates such as February 30.
- Observed behavior
- Manual day-number incrementing does not automatically stop at the correct month end and can create dates that do not exist in shorter months.
Open the workbook in WPS Office Excel and confirm that the start date is entered as a real date rather than text. Make sure the cells where the date list will spill are empty.
Generate a Complete Month Date List with EOMONTH and SEQUENCE
Use this when you need a dynamic date list that automatically respects the number of days in the selected month.
This formula creates a horizontal date sequence from the start date, uses EOMONTH to find the month end, and uses SEQUENCE to expand by the correct number of days.
Click an empty cell and type the first day of the month, for example 2/1/2023, then press Enter. The cell must be formatted as a date.
In the cell where the date list should begin, enter =startdate+SEQUENCE(1,DAY(EOMONTH(startdate,0)),0), replacing startdate with the cell reference of your first day.
Press Enter to apply the formula. WPS Excel will spill the dates across the row and automatically stop at February 28 or 29 in a leap year, or the proper final day for other months.
Calculate the Number of Days in a Month with DAY and EOMONTH
Use this when you only need the day count before filling a range or validating an existing date list.
Generate Month-Specific Dates in WPS Office Excel
You can create a month-aware date list in WPS Office Excel with EOMONTH and SEQUENCE. The formulas automatically stop at the correct final day for each month, so February and shorter months never produce invalid dates.
- 1. Open WPS Office Excel: Open WPS Office and create or open the workbook where you need the date list.
- 2. Enter the month start date: Type the first day of the target month as a real date in a blank cell.
- 3. Apply the EOMONTH and SEQUENCE formula: In the start cell for the date series, enter =startdate+SEQUENCE(1,DAY(EOMONTH(startdate,0)),0) using your start-date cell reference.
- 4. Press Enter and let the dates spill: Press Enter. WPS Office Excel will fill the row with the correct dates and stop at the actual month end.
- 5. Save the workbook as .xlsx for compatibility: Go to Menu > Save As and select Excel Workbook (*.xlsx) to keep the file compatible with Microsoft Excel.
Frequently Asked Questions
Why does my date list include February 30 when I drag the fill handle?
Dragging or manually incrementing day numbers may not align with the actual month end. Instead, use =startdate+SEQUENCE(1,DAY(EOMONTH(startdate,0)),0) to generate only real dates for the month.
How do I adjust the formula for a vertical date list instead of horizontal?
Use =startdate+SEQUENCE(DAY(EOMONTH(startdate,0)),1,0). This places the day count in the rows argument so the dates spill downward.
Will this formula handle leap years automatically?
Yes. EOMONTH(startdate,0) returns February 29 in leap years and February 28 otherwise, so DAY returns 29 or 28 and SEQUENCE uses that correct count.
Do I need Microsoft Excel to use EOMONTH and SEQUENCE?
No. WPS Office Excel supports these functions, and the formulas work when you open or save .xlsx files in either WPS Office or Microsoft Excel.
What should I do if the formula returns a #SPILL! error?
Make sure the cells where the sequence should appear are completely empty. Clear any existing data in the target range and press Enter again.
