The first month is one late
A default sequence starts at one. Use zero so B3 alone defines the first selected month.
Generate a configurable month-end date series
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one spilling formula in E2. Return one horizontal row of exactly B4 numeric month-end dates: item i uses original B2 anchor and month offset B3 + i × B5, with i starting at zero. The first offset is signed; count is 1–12 and positive month step is 1–12. Clear all inactive cells within E2:P2 when a shorter series replaces a longer one. Anchor dates and every first/last target month stay within 1901–2099. Text/Boolean/blank/fractional inputs, zero count/step and out-of-bounds combinations remain unapplied. Preserve numeric dates and the format of future spill cells in copies.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: E2:P2. Enter one formula in E2; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Inspect the current month-end spill
Apply one formula in E2. Count changes alter the actual horizontal spill; inactive result cells must clear. Every date is calculated from the original anchor and its selected month offset.
| Result | Current stored value | Applied formula |
|---|---|---|
| E2 · Month end 1 | — | Spill result / inactive |
| F2 · Month end 2 | — | Spill result / inactive |
| G2 · Month end 3 | — | Spill result / inactive |
| H2 · Month end 4 | — | Spill result / inactive |
| I2 · Month end 5 | — | Spill result / inactive |
| J2 · Month end 6 | — | Spill result / inactive |
| K2 · Month end 7 | — | Spill result / inactive |
| L2 · Month end 8 | — | Spill result / inactive |
| M2 · Month end 9 | — | Spill result / inactive |
| N2 · Month end 10 | — | Spill result / inactive |
| O2 · Month end 11 | — | Spill result / inactive |
| P2 · Month end 12 | — | Spill result / inactive |
Change an actual source value
Calendar input writes a whole numeric anchor date in 1901–2099. Use an integer first offset −24–24, count 1–12 and positive month step 1–12. All target months must stay within 1901–2099; invalid combinations do not create a partial series.
This draft matches the current cell.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Spill consecutive, quarterly or annual month ends from a numeric date, with an explicit first offset, length and month step.
THE QUICK ANSWER01
SEQUENCE makes zero-based month offsets. EOMONTH calculates every date from the original anchor, keeping leap-year month ends and configurable horizontal shape.
ƒx
=EOMONTH(B2,B3+SEQUENCE(1,B4,0,B5))| Period 1 | Period 2 | Period 3 | Period 4 | Period 5 | Period 6 |
|---|---|---|---|---|---|
| 2023-01-31 | 2023-02-28 | 2023-03-31 | 2023-04-30 | 2023-05-31 | 2023-06-30 |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
A sequence of calendar months
The series has its own first offset, item count and positive month step. It does not add fixed 30-day or 365-day periods. Signed first offsets can begin before the anchor month without reversing the series order.
Anchoring keeps leap-day month ends
Starting with February 2020 and an annual step, the five dates end on February 29, 28, 28, 28 and 29. Repeatedly carrying a clamped 28th without recalculating the month end loses that final leap day.
Shape and blank tails matter
A six-date result cannot pass every input count. After changing from twelve to two, all ten inactive cells must clear. Date format metadata remains on those cells so a formula copy can expand again without reverting to visible serial numbers.
Read the full step-by-step walkthrough
- Retain the original anchor
The day inside the anchor month does not become a copied day-of-month policy. Every result is the final day of its selected calendar month.
- Choose the first offset
B3 is signed: zero means the anchor month, negative values begin earlier and positive values begin later.
- Generate a horizontal shape
Rows = 1 and columns = B4. A count of one is a valid single-cell result; a count of twelve fills the declared maximum width.
- Start from zero and choose the month step
The first sequence term is zero and B5 is the increment. Three makes quarterly dates; twelve revisits the same month across years.
- Avoid chained month-end drift
Use the original anchor for every EOMONTH call. An annual February sequence must return to February 29 in the next leap year rather than remain at a prior clamped 28th.
- Change the count and inspect cleared tails
A shorter count changes the actual spill extent. Previously active cells outside it must become blank. The current formula copy keeps all potential spill cells date-formatted for later edits.
Things to watch
The output length is fixed
Use the actual count input and verify all retained sources, including one and twelve dates.
The annual leap day disappears
Compute each month end from the original anchor and its selected month, not a progressively clamped prior date.
About this example
Original bounded numeric clock/calendar task, complete current/source results and formula/frozen files; native-client rendering, recalculation and edit/save remain separate.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.