Excel Get Fiscal Year from Date: Formula Guide

To get the fiscal year from a date in Excel, use a formula that tests whether the month is before or after your fiscal year start month, then adjusts the year accordingly. For a fiscal year starting in July, the formula =YEAR(A2)+(MONTH(A2)>=7) returns the correct fiscal year for any date in cell A2.

Formula

The general formula to extract the fiscal year from a date in Excel is:

=YEAR(date) + (MONTH(date) >= fiscal_start_month)

Where:

• date is the cell containing the date you want to convert.
• fiscal_start_month is the month number (1–12) when your fiscal year begins. For example, 7 for July, 4 for April, or 10 for October.

The comparison (MONTH(date) >= fiscal_start_month) returns TRUE (which Excel treats as 1) if the month is on or after the start month, and FALSE (0) otherwise. Adding that to the calendar year gives the fiscal year.

Worked Example

Suppose your fiscal year starts in July (month 7) and cell A2 contains the date March 15, 2024.

1. YEAR(A2) returns 2024.
2. MONTH(A2) returns 3.
3. Check 3 >= 7 → FALSE, which is 0.
4. Result: 2024 + 0 = 2024.

So March 15, 2024 falls in fiscal year 2024 (which runs July 2023–June 2024).

Now take the date August 1, 2024:

1. YEAR(A2) returns 2024.
2. MONTH(A2) returns 8.
3. Check 8 >= 7 → TRUE, which is 1.
4. Result: 2024 + 1 = 2025.

August 1, 2024 falls in fiscal year 2025 (July 2024–June 2025).

Why the Fiscal Year Differs from the Calendar Year

Many organizations use a fiscal year that does not align with January 1. Governments, schools, and businesses often choose a start date that matches their budgeting cycle, tax season, or operational needs. For example, the U.S. federal government’s fiscal year starts October 1, while many school districts start July 1. When you work with financial data, knowing the fiscal year is essential for correct reporting, budgeting, and comparison across periods.

How to Choose the Right Formula for Your Fiscal Year

The formula above works for any start month, but you must know which month your fiscal year begins. If your fiscal year starts in:

• January – use =YEAR(date) (same as calendar year).
• April – use =YEAR(date)+(MONTH(date)>=4).
• July – use =YEAR(date)+(MONTH(date)>=7).
• October – use =YEAR(date)+(MONTH(date)>=10).

If your fiscal year is defined by a specific day (e.g., the first Monday of April), the simple month-based formula will not be precise. In that case, you would need a more complex formula using DATE and WEEKDAY functions, or a lookup table.

Common Mistakes When Extracting Fiscal Year

A frequent error is using YEAR() alone, which gives the calendar year, not the fiscal year. Another mistake is forgetting that the comparison returns TRUE/FALSE; in Excel, TRUE equals 1 and FALSE equals 0, so the addition works correctly. But if you copy the formula to another cell and the date reference changes incorrectly, you may get wrong results. Always use absolute references (e.g., $A$2) if the start month is stored in a cell.

Also, be careful when your fiscal year spans two calendar years. For example, fiscal year 2025 might run from July 1, 2024 to June 30, 2025. The formula labels all dates from July 2024 through June 2025 as fiscal year 2025, which is correct.

Comparing Fiscal Year Formulas for Different Start Months

Here is a quick comparison of formulas for common fiscal year starts:

• Start July: =YEAR(A2)+(MONTH(A2)>=7)
• Start October: =YEAR(A2)+(MONTH(A2)>=10)
• Start April: =YEAR(A2)+(MONTH(A2)>=4)

All follow the same pattern. The only difference is the month number in the comparison.

Alternatives to the Simple Formula

If you prefer a more explicit approach, you can use IF:

=IF(MONTH(A2)>=7, YEAR(A2)+1, YEAR(A2))

This does the same thing but is longer. Another alternative is to use the EDATE function to shift the date back to the start of the fiscal year, then extract the year: =YEAR(EDATE(A2, -6)) for a July start (since July is 6 months after January). This method is useful if you also need the fiscal quarter.

Frequently Asked Questions

How do I get the fiscal year in Excel if my fiscal year starts in July?

Use the formula =YEAR(A2)+(MONTH(A2)>=7). Replace 7 with your actual start month.

What if my fiscal year starts on a specific day, like April 1?

The simple month-based formula works if the start is the first of the month. If it is a different day (e.g., April 6), you need a more complex formula that compares the full date, such as =YEAR(A2)+(A2>=DATE(YEAR(A2),4,6)).

Can I use this formula for fiscal quarters too?

Yes. Once you have the fiscal year, you can calculate the fiscal quarter with =ROUNDUP((MONTH(A2)-start_month+1)/3,0) or similar, but be careful with negative values.

Why does my formula return a decimal or text?

If you see a decimal, check that the date cell is formatted as a date, not as text. If the formula returns text, ensure you haven’t accidentally included quotes around the numbers.

Does this work in Google Sheets?

Yes, the same formulas work in Google Sheets because they use the same function names and logic.

Related

• Show Values as Percentage in PivotTable: Excel Guide
• Wi-Fi Access Point: What It Is and How It Works
• LoL Worlds Scores & Team Status: Where to Find Them

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2