Convert dates to months in Excel: from transaction dates to accounting periods

A transaction list records the exact day each payment, invoice or fee happened. Reports rarely care about the day. They care about the period: which month, which quarter, which fiscal year. This page shows how to turn each transaction date into the period it belongs to, with a month-end date, a year and month, a sortable label, or a fiscal year and quarter, and the small mistakes that put transactions in the wrong period.

Why accountants map dates to periods

An accounting period is the stretch of time a set of numbers covers, usually a month. Every transaction belongs to exactly one period, and most of the work around reporting depends on getting that assignment right.

• Month-end close. Closing the books means finishing every entry for a month so its numbers stop changing. To close March, you need to know which transactions are March transactions.
• Summaries by month. A manager wants spending by month, not a list of three hundred dates. SUMIFS, pivot tables and charts all group on a value, so each row needs a period column to group on.
• Cut-off at year end. Cut-off means recording each transaction in the period it actually belongs to. A payment on December 31 and one on January 1 are a day apart, but they land in different years. Getting that boundary wrong moves income or expense into the wrong year's results.

So the job is simple: add a column that says which period each date falls in, with formulas that stay right at the edges of months and years.

Why this matters for MO-220 practice

The Excel for Accounting exam (MO-220) includes preparing data for analysis, and working with dates is part of transforming that data [Official]. It is a 50-minute, performance-based exam, and its tasks describe the purpose instead of naming the function [Official]. You might read "show the last day of the month for each transaction" and have to recognize that EOMONTH is the tool. The exercises in this lesson are worded the same way. Accounting Exercises is not affiliated with Microsoft or Certiport.

How it works

Dates are numbers

The spreadsheet stores every date as a serial number: a count of days since a fixed starting point at the end of 1899. December 31, 2025 is stored as 46022, and January 1, 2026 is 46023. The date you see is only a number format laid over that count.

That is why date arithmetic works: adding 1 moves a date one day forward. It also explains a common surprise. If a formula returns 46022 instead of a date, the answer is right and only the date format is missing.

EOMONTH: the last day of the period

Many companies label a period by its last day: "the period ending March 31". EOMONTH returns the last day of a month a given number of months away from a start date:

=EOMONTH(A5,0)

The second argument is the number of months to move. 0 means "stay in this month", so any date in March returns March 31. 1 would jump to the end of April. EOMONTH knows how many days each month has, including February in leap years.

YEAR, MONTH and DATE

YEAR and MONTH pull one part out of a date as a plain number. =YEAR(A5) returns a year such as 2026, and =MONTH(A5) returns 1 to 12, which later formulas can use to work out a quarter.

DATE goes the other way: it builds a date from a year, a month and a day. =DATE(YEAR(A5),MONTH(A5),1) returns the first day of the month. That is a valid way to label a period, but only when the company labels periods by their first day.

TEXT: a label for sorting or for display

TEXT turns a number into text using a format code. For dates, the format code decides the layout:

• =TEXT(A5,"yyyy-mm") returns a label such as 2026-03. The year comes first and the month always has two digits, so the labels sort in date order even when sorted as plain text.
• =TEXT(A5,"mmm yyyy") returns Mar 2026. That reads nicely on a printed report, but it sorts alphabetically: Apr, Aug, Dec, Feb. Use it for display, not for sorting or grouping.

Remember that TEXT returns text, not a date. That is fine for a label you group on, but keep the original date column for date arithmetic.

Fiscal years that don't start in January

A fiscal year is the twelve-month year a company reports on. Many start in a month other than January, such as April, July or October. Most companies name the fiscal year for the calendar year in which it ends: a year running from July 2026 to June 2027 is fiscal 2027.

With the start month in $B$2, the fiscal year is the calendar year, plus one if the month has reached the start month:

=YEAR(A5)+IF(MONTH(A5)>=$B$2,1,0)

The fiscal quarter counts how many months have passed since the fiscal year began, then groups them in threes:

=INT(MOD(MONTH(A5)-$B$2,12)/3)+1

Read it from the inside out. MONTH(A5)-$B$2 is how far the month is from the start month. It is negative for months before the start, so MOD(…,12) wraps it around into 0 to 11: months since the fiscal year began. Dividing by 3 and taking INT gives 0 to 3, and adding 1 gives quarters 1 to 4. The dollar signs keep the reference to B2 fixed when you fill the formula down.

Worked example 1: period-end dates

Bluebird Veterinary labels each period by its last day. Its transaction list has dates in A5:A16, and D5:D16 needs the period end. The formula in D5 is =EOMONTH(A5,0), filled down. Some of the rows:

Look at the edges. A date that is already a month-end, like 7/31/2026, returns itself. A first of the month, like 5/1/2025, still belongs to May. And the December 31 and January 1 rows land in different years, which is exactly the cut-off you want.

Worked example 2: year, month and a sortable label

Copperleaf Studio wants three columns: the year in D, the month number in E and a text label in F. The formulas are =YEAR(A5), =MONTH(A5) and =TEXT(A5,"yyyy-mm").

The labels in F sort correctly as text: 2025-02 before 2025-10, and 2025-12 before 2026-01. The two-digit month and the year-first layout are what make that work.

Worked example 3: fiscal year and quarter

In a second Copperleaf Studio sheet, the fiscal year starts in May (B2 holds 5). Fiscal 2027 runs from May 2026 to April 2027. The formulas are =YEAR(A5)+IF(MONTH(A5)>=$B$2,1,0) in D and =INT(MOD(MONTH(A5)-$B$2,12)/3)+1 in E.

Follow one row. For 1/4/2026, the month is 1. 1-5 is -4, and MOD(-4,12) is 8: January is eight months after May. INT(8/3) is 2, plus 1 gives quarter 3. The month is before May, so no year is added and the fiscal year is 2026.

Notice that December 31, 2026 and January 1, 2027 share a fiscal year and quarter. Here the boundary that matters is between April and May.

Common mistakes

The first of the month instead of the month-end

=DATE(YEAR(A5),MONTH(A5),1) returns 2/1/2025 for a February transaction. It finds the right month, but if periods are labelled by their last day, every label is wrong and a lookup against month-ends finds nothing. Use EOMONTH(A5,0) when the period is named by its end.

EOMONTH with 1 instead of 0

=EOMONTH(A5,1) moves one month forward first. A transaction on 2/14/2025 comes back as 3/31/2025, and the 12/31/2025 transaction moves into January 2026, across the year-end cut-off. The second argument counts months to move, so the same month is 0.

No leading zero in month labels

TEXT(A5,"yyyy-m") gives 2025-2 and 2025-10. Sorted as text, 2025-10 comes before 2025-2, because the character 1 comes before 2. Use mm so every month has two digits: 2025-02.

Month-first labels

TEXT(A5,"mm-yyyy") gives 12-2025 and 01-2026. Sorted, 01-2026 comes before 12-2025, so January of the next year jumps ahead of December. Put the year first, "yyyy-mm", so the most significant part leads.

Using the calendar year for a fiscal year

=YEAR(A5) puts 5/19/2026 in 2026. With a fiscal year starting in May and named for its end year, that transaction belongs to fiscal 2027. Every transaction from the start month onward needs the extra year from IF(MONTH(A5)>=$B$2,1,0).

Using calendar quarters

=ROUNDUP(MONTH(A5)/3,0) makes January to March quarter 1. That is right for a calendar year and wrong for any other. In the May example it calls 5/19/2026 quarter 2, when it is the first quarter of the fiscal year. Count months from the start month with MOD(MONTH(A5)-$B$2,12) instead.