Generate Monthly Start and End Dates in Microsoft Excel
Question details
Create a formula that lists the effective start date and end date for each month within a selected date range.
- Product
- Microsoft Excel for Microsoft 365
- Device & OS
- not provided
- Scenario
- The user has a start date and an end date and needs to split the period into monthly date ranges, including incomplete first and last months.
- Observed behavior
- The result should show each month with its applicable start date and end date, limited to the selected overall date range.
Enter the overall start date in B2 and the overall end date in B3, and make sure both cells contain valid Excel date values rather than text. Use Microsoft 365 for the single-cell dynamic array formula because it requires LET, SEQUENCE, and VSTACK.
Use a Dynamic Array Formula to Return Every Monthly Date Range
Use this method in Microsoft 365 when you want one formula to spill month names, adjusted start dates, and adjusted end dates automatically.
The formula starts with the first day of the month containing the start date, generates every required month, and then limits the first and last monthly ranges to the dates in B2 and B3.
In the worksheet, enter the period start date in cell B2 and the period end date in cell B3.
Click an empty cell with sufficient clear space to the right and below it, such as D2, so the dynamic array can spill without blocked cells.
Paste this formula into the selected cell: =LET(startdate,B2,enddate,B3,firstofmonth,startdate-DAY(startdate)+1,monthstart,EDATE(firstofmonth,SEQUENCE(,DATEDIF(firstofmonth,enddate,"M")+1,0)),startdates,IF(monthstart<startdate,startdate,monthstart),monthend,EOMONTH(startdates,0),enddates,IF(monthend>enddate,enddate,monthend),VSTACK(TEXT(startdates,"mmmm"),startdates,enddates))
Select the spilled start-date and end-date results, then use Home > Number Format > Short Date or choose More Number Formats to apply the date style you need.
Build Monthly Ranges with Helper Formulas in Excel or WPS Spreadsheet
Use this approach when dynamic array functions are unavailable or when you prefer a vertical month-by-month table.
Create Monthly Start and End Date Ranges in WPS Spreadsheet
WPS Spreadsheet can split a date period into monthly ranges with familiar Excel-style formulas. Use a simple helper table to calculate each month, preserve partial first and last months, and format the output for reporting or scheduling.
- 1. Open WPS Spreadsheet: Open WPS Office and select Spreadsheets > New Blank Workbook, or open the existing Excel workbook containing the start and end dates.
- 2. Enter the overall period: Place the start date in B2 and the end date in B3, then confirm both cells use a Date format from Home > Number Format.
- 3. Create the first monthly range: Add headings in D1:F1, then enter =DATE(YEAR($B$2),MONTH($B$2),1) in D2, =$B$2 in E2, and =MIN(EOMONTH(D2,0),$B$3) in F2.
- 4. Generate following months: In row 3, use =EDATE(D2,1) for the month, =MAX(D3,$B$2) for the start date, and =MIN(EOMONTH(D3,0),$B$3) for the end date; drag the row formulas downward.
- 5. Format the output: Select the month column and use Home > Number Format to show mmmm yyyy, then format the start and end columns as dates.
Frequently Asked Questions
Why does the dynamic array formula return a #SPILL! error?
A #SPILL! error means cells in the formula's output range are not empty. Clear or move the cells blocking the spill area, then re-enter the formula.
How are partial first and last months handled?
The first monthly start date is limited to the date in B2, and the final monthly end date is limited to the date in B3. For example, a range from January 15 to March 10 returns January 15–31 and March 1–10.
Can I return the monthly dates in vertical rows instead of horizontal columns?
Yes. Use the helper-table method with Month, Start Date, and End Date in separate columns. This creates one monthly period per row and is easier to filter or export.
Which Excel versions support LET, SEQUENCE, and VSTACK?
These dynamic array functions are available in Microsoft 365 versions of Excel. If your version does not recognize one of the functions, use the helper formulas with EDATE and EOMONTH instead.
How do I display the month name with the year?
Select the month-date cells, open Home > Number Format > More Number Formats, and apply the custom format mmmm yyyy.
