Fill months in Excel: build a 12-month calendar that never drifts

Almost every accounting schedule runs month by month. A closing calendar, a forecast, an accrual schedule and a loan table all start with the same column: one date per month. Typing twelve dates by hand is slow and easy to get wrong. This page shows how to fill months in Excel from a single starting row, which tool to use for first-of-month dates and which for month-ends, and the small mistakes that quietly move your dates off the calendar.

Why accountants build period calendars

Accounting works in periods. A period is a stretch of time you report on, usually a month, and closing the books means finishing all the entries for that period so its numbers stop changing. Every month has a start date and an end date, and many tasks hang off those two dates.

• Closing schedules. A close calendar lists each month-end and the tasks due around it: reconcile the bank, post accruals, review the trial balance. The month-end date is the anchor for all of it.
• Forecasts and budgets. A forecast projects revenue or costs for each future month. The first column is the month, and every other column depends on it.
• Accruals and prepaid schedules. An accrual records an expense or income in the month it belongs to, even if cash moves later. A schedule that spreads an annual insurance bill over twelve months needs twelve correct month-end dates.
• Period numbers. Many reports also need a simple counter, period 1 to period 12, so formulas and lookups can refer to "period 7" instead of a date.

The dates must be exact. A schedule dated the 28th when the books close on the 31st looks careless at best. At worst, a lookup or a date filter misses three days of transactions. So the goal is not just a column of dates that look roughly monthly. It is a column where every date lands on the right day.

Two ways to fill months: the Fill tools and formulas

The Fill handle and the Series dialog

The spreadsheet has built-in tools for this. The Fill handle is the small square at the bottom-right corner of a selected cell. Type a date, drag the handle down, and the spreadsheet continues the sequence. After dragging, an options button lets you choose to fill by days, weekdays, months or years.

The Series dialog (on the Home tab, under Fill) gives more control. You choose a date unit, such as Month, and a step value, such as 1 for every month or 3 for every quarter. You can also set a stop value so the series ends where you want.

These tools are quick and worth knowing. Their weakness is that they type fixed values. If the start date changes next year, nothing below it updates. You have to fill the column again.

The formula approach

A formula links each row to the row above it. Change the first date, and the whole calendar moves with it. This is the approach the exercises grade, because a linked calendar is the one you can reuse. Four formulas cover the common cases:

• =EDATE(A5,1) returns the same day of the next month. Use it for period starts on the first of the month. The second argument is the number of months to move; 3 moves a quarter, -1 moves back a month.
• =EOMONTH(A5,1) returns the last day of the next month. Use it for month-ends. With 0 it returns the end of the same month, which is handy for turning any date into its month-end.
• =B5+1 counts periods. Each row is the period above plus one.
• =B5*(1+$B$2) grows an amount by the rate in B2. The dollar signs lock the rate cell so it stays put when you fill down.

Write each formula once, in the first empty row, then fill it down. The references to the row above shift by one row each time, which is exactly what you want. The locked rate reference does not shift, which is also what you want.

Worked example 1: a reporting calendar from the first of the month

Juniper Landscaping needs a 12-month reporting calendar that starts on the first day of each month. Row 5 holds the first period: A5 is 9/1/2025 and B5 is 1.

In A6 the formula is =EDATE(A5,1). In B6 it is =B5+1. Fill both down to row 16.

Every date is the first of its month, and the calendar crosses into the new year without any special handling. EDATE works well here because the first of the month exists in every month.

Worked example 2: month-end close dates, and why EDATE drifts

Maple Lane Catering closes its books on the last day of every month and needs the next 11 close dates. A5 holds 6/30/2026. In A6 the formula is =EOMONTH(A5,1), filled down to A16.

Now compare it with =EDATE(A5,1), which keeps the day number of the row above.

Because the start date is the 30th, EDATE returns the 30th of each month. That is only a month-end in 30-day months. Then February arrives. There is no February 30, so EDATE returns the last day it can, 2/28/2027. From that row on, every formula copies the 28th forward: 3/28, 4/28, 5/28. One short month has pulled the rest of the calendar three days early.

EOMONTH does not have this problem. It ignores the day number and asks a different question: what is the last day of the next month? The answer is 31, 30 or 28, whatever the month has.

Worked example 3: a growing monthly forecast

Harbor Bakery forecasts revenue growing by the same example percentage every month. B2 holds an example monthly growth rate of 0.5%. Row 5 holds the first month: A5 is 2/28/2025 and B5 is $66,301.23.

In A6 the formula is =EOMONTH(A5,1). In B6 it is =B5*(1+$B$2). Fill both down to row 16.

Notice the start date. It is February 28, so the 28th is the month-end here. EDATE would give 3/28/2025 and stay on the 28th all year. EOMONTH jumps straight to 3/31/2025.

Each month's revenue is the month before times 1.005. The growth compounds, so each step adds a little more in dollars than the last: $331.51 in the first month, and about $348 by the last. The amounts shown are rounded to the cent; the cells hold the full values.

Common mistakes

Adding 30 or 31 days

=A5+30 looks like "next month", but months have 28 to 31 days. In the Juniper calendar it works once, from 9/1/2025 to 10/1/2025, because September has 30 days. Then it lands on 10/31/2025, 11/30/2025 and 12/30/2025, and keeps slipping. By row 16 it shows 7/28/2026 instead of 8/1/2026. Adding 31 fails the other way: 10/2/2025, 11/2/2025, 12/3/2025, and so on, a little later each short month. Move by whole months with EDATE or EOMONTH instead.

Using EDATE on month-ends

This is the drift shown in the Maple Lane table. EDATE keeps the day number, and once a short month cuts that day to the 28th, every later row stays on the 28th. If the column is meant to hold month-ends, use EOMONTH(previous,1). Keep EDATE for dates that exist in every month, such as the 1st or the 15th.

Leaving the growth rate unlocked

If B6 says =B5*(1+B2) without dollar signs, the first row is right. Fill it down, though, and the rate reference moves with it. B7 now points at B3, which is blank, so April shows the same $66,632.74 as March. B8 points at B4, the "Revenue" heading, and text cannot be added to a number, so the rest of the column shows #VALUE!. Lock the rate as $B$2 so every row reads the same cell.

Adding the rate instead of growing by it

=B5+$B$2 adds 0.005 to the revenue, which is half a cent, not half a percent. After eleven months the forecast has grown by about six cents and ends at $66,301.29 instead of $70,040.34. A percentage only means something when you multiply by it. To grow by a rate, multiply by one plus the rate: =B5*(1+$B$2).

How this fits MO-220 practice

The MO-220 exam is a 50-minute, performance-based test [Official], so you work in a live workbook rather than answer multiple-choice questions. Its tasks describe the purpose instead of naming the function [Official]. A task might ask for "the last day of each following month" and leave you to recognize that as EOMONTH. Practising the purpose behind each formula, not just the formula, is what prepares you for that. Accounting Exercises is not affiliated with Microsoft or Certiport.

Each exercise in this lesson gives you one starting row and asks you to write a formula once and fill it down. A check compares every cell with the correct calendar, so you see straight away whether a date has drifted.