Fiscal Year and Fiscal Quarter From a Date in Excel

To get the fiscal year from a date in Excel, move the date forward by enough months that the fiscal year lines up with a calendar year, then read the year. For a fiscal year that starts in July, =YEAR(EDATE(A2,6)) does it in one step. This guide explains the idea, gives the formula for any start month, and shows fiscal quarters, labels and totals.

What is a fiscal year?#

A fiscal year is the 12-month period an organisation uses for its accounts and budgets. It often doesn't match the calendar year. Many organisations start their fiscal year in April, July or October instead of January.

A fiscal year that crosses two calendar years needs a name. The most common convention is to name it by the year it ends: July 2024 to June 2025 is FY2025. Some organisations name it by the year it starts, or write both, like 2024/25. Check which one your company uses, because the formulas differ. Finance teams usually state the convention in their reporting calendar or budget templates. If you're unsure in a job test, say which convention you assumed, for example in a note next to the formula.

How to get the fiscal year with YEAR and EDATE#

Shift the date forward with EDATE, then take the year with YEAR. EDATE moves a date by a whole number of months. For a fiscal year starting in July:

=YEAR(EDATE(A2,6))

Moving six months forward sends July to January of the next year, and June to December of the same year. So every date in July 2024 to June 2025 lands somewhere in 2025:

The EDATE function guide explains how it handles month lengths.

A shorter version for July uses a TRUE/FALSE test:

=YEAR(A2)+(MONTH(A2)>=7)

MONTH(A2)>=7 is TRUE (counted as 1) from July to December, and FALSE (0) from January to June. So dates in the second half of the year get the next year's number. Both formulas give the same results.

Fiscal year formula for any start month#

For a fiscal year that starts in month S and is named by its end year, shift by 13 − S months:

The shift is the number of months from the start month to the next January. For an October start, 1 October 2024 plus 3 months is 1 January 2025, so it belongs to FY2025.

If your fiscal year starts in January, it's just the calendar year: use =YEAR(A2).

If your company names the year by its start, shift backwards instead, by S − 1 months. For a July start, =YEAR(EDATE(A2,-6)) returns 2024 for any date from July 2024 to June 2025.

How to get the fiscal quarter#

Use the same shift, then turn the month into a quarter. Dividing a month number by 3 and rounding up gives the quarter:

=ROUNDUP(MONTH(EDATE(A2,6))/3,0)

For a July start, July to September become months 1 to 3 after the shift, so they're Q1. October to December are Q2, and so on. For 12 September 2024, the shifted month is March, which is 3, so the result is 1.

For other start months, use the same 13 − S shift inside EDATE. The calendar version of this formula is covered in getting the quarter from a date.

How to build labels like FY2025 or 2024/25#

Join text to the result with & to make readable labels. For a July start:

The last formula finds the start year, then adds the last two digits of the following year. These labels are text, so use them for display and grouping, not for date maths. For month labels, see getting the month name from a date.

How to total sales by fiscal year#

Use SUMIFS with the first day of the fiscal year and the first day of the next one. For FY2025 with a July start, amounts in C2:C100 and dates in B2:B100:

=SUMIFS(C2:C100,B2:B100,">="&DATE(2024,7,1),B2:B100,"<"&DATE(2025,7,1))

Using "less than the next start date" catches every day of June, even if the dates include times. For a report with several years, put the fiscal year in a helper column with =YEAR(EDATE(B2,6)) and use a PivotTable or SUMIF on that column.

To find the start and end dates of any date's fiscal year, for a July start: the start is =DATE(YEAR(EDATE(A2,-6)),7,1) and the end is =EOMONTH(start_cell,11), which is 30 June of the following year. The guide to adding days, months and years explains EDATE and EOMONTH in more detail.

Common mistakes with fiscal years#

Most errors come from the naming convention or from the shift. Check these:

1. Wrong naming convention. Start year and end year give results one apart. Confirm which your company uses.
2. Shifting the wrong way. Use a positive shift for end-year names and a negative one for start-year names.
3. Adding days instead of months. A2+182 isn't six months and drifts by a day or two. Use EDATE.
4. Text dates. EDATE returns #VALUE! on text. Convert to real dates first.
5. Result looks like a date. If 2025 shows as a 1905 date, format the cell as General.

Practise it#

Fiscal periods come up in finance and reporting job tests, and they combine several date skills at once. Practise date basics in a live spreadsheet, then practise EDATE, EOMONTH and WEEKDAY to get the shifting right. Each formula is checked instantly. Or try the exercise on this page first.