How to Create a Month Column from Repeating Day Values in Excel
Question details
The user needs to assign month values in a spreadsheet where day numbers repeat from 1 to 31 and each day occupies eight rows.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has a column of repeating day numbers and wants to add an adjacent month column so that each eight-row block is labeled with the correct month.
- Observed behavior
- Day numbers alone are not enough to identify the month because values such as 1 through 31 repeat, making month assignment ambiguous.
Check whether your WPS Office version supports dynamic array functions such as LET, SEQUENCE, REDUCE, EXPAND, and VSTACK. If not, use the row-based TEXT, DATE, and ROW formula for wider compatibility.
Use a Row-Based Date Formula to Assign Month Names
Best for WPS Office Spreadsheet and Microsoft Excel because it uses only TEXT, DATE, and ROW; it creates a sequential date from the row position and displays the month name directly.
Because day values repeat each month, the month cannot be determined from the day number alone. This formula treats the first data row as January 1, 2023, advances the date every eight rows, and returns the month name.
For a leap year, use the leap-year start date and fill the formula through 2,928 rows instead of 2,920.
Click B1, or the first cell in the month column that lines up with the first day value.
Type =TEXT(DATE(2023,1,1)+INT((ROW()-1)/8),"mmmm") and press Enter.
Drag the fill handle from B1 through row 2920 for a non-leap year, or row 2928 for a leap year, so each eight-row block receives a month name.
Check that the label changes after the expected number of rows, for example from January to February after the first 31 day blocks.
Use Dynamic Array Functions for a Spilled Month Column
Use this method when your version of WPS Office or Microsoft 365 supports LET, SEQUENCE, REDUCE, EXPAND, and VSTACK.
Assign Months from Repeating Day Values in WPS Office Spreadsheet
WPS Office Spreadsheet can generate a sequential date from the row position, repeat each day across eight rows, and return month names without manual retyping. It opens and saves Excel workbooks, so you can keep using familiar formulas and formatting.
- 1. Open your workbook: In WPS Office, click Menu > Open and select the .xlsx workbook that contains the repeating day values.
- 2. Select the month column start cell: Click the first cell where the month column should appear, such as B1, and ensure the day values are in an adjacent column.
- 3. Enter the row-based date formula: Type =TEXT(DATE(2023,1,1)+INT((ROW()-1)/8),"mmmm") and press Enter.
- 4. Fill the formula down: Drag the fill handle down through all 2,920 rows for a non-leap year, or 2,928 rows for a leap year.
- 5. Check the output: Verify that each day number repeats across eight rows and that the month label changes at the correct calendar boundary.
Frequently Asked Questions
Why can't I just use the repeating day number to identify the month?
Day numbers repeat every month, so a value such as 15 can occur in January, February, March, and every other month. You need a real date sequence or a row-based date calculation, formatted as a month name, to remove the ambiguity.
How do I adjust the formula for a leap year?
Change DATE(2023,1,1) to DATE(2024,1,1) and fill the formula through 2,928 rows instead of 2,920 rows.
What formula works if my WPS Office version does not support LET, SEQUENCE, REDUCE, EXPAND, or VSTACK?
Use =TEXT(DATE(2023,1,1)+INT((ROW()-1)/8),"mmmm") in the first month cell and fill it down through all rows.
How do I create a spilled month column with dynamic array functions?
Use =LET(dates,SEQUENCE(365,,DATE(2023,1,1)),REDUCE("Month",dates,LAMBDA(a,d,VSTACK(a,EXPAND(d,8,,d))))) and format the spilled range with the custom date format mmmm.
Can I use this method for a different year or a different number of rows per day?
Yes. Replace DATE(2023,1,1) with the desired start date, update the total row count to match the year length, and change the number 8 to the number of rows you need per day.
