I was recently working on a project where I had some dates and had to classify each date into its fiscal year (also sometimes called the financial year).
While Excel does not have a dedicated function to do this, it can quickly be done using a combination of functions.
In this article, I will show you how to calculate the fiscal year from a date in Excel using a formula, with variations for a fixed start month or a different start month per date.
I’ll also show you fiscal year labels like 2025-26 and FY26, and how to handle a fiscal year that starts mid-month.
Follow along with the example file
Fiscal Year from Date Excel.xlsx
Fiscal Year Formula for a Fixed Start Month
For this example, I will assume that the new fiscal year for all the dates starts in July.
On the “Fixed Start Month” sheet, I have transaction dates in A2:A11. I want to get each date’s fiscal year in column B.
We can’t just extract the year from the date, as the fiscal year is different from the calendar year.
For a July start, dates before July stay in the current year’s fiscal period. Dates in or after July belong to the next year’s fiscal period.
So, a transaction date of 05-Mar-2025 would be in the fiscal year 2025, while 05-Sep-2025 would be in the fiscal year 2026.
In Excel for Microsoft 365, Excel 2024, or Excel 2021, enter this formula in B2, under “Fiscal Year (July Start)”:
=YEAR(A2:A11)+(MONTH(A2:A11)>=7)
Press Enter, and the formula fills the whole result column, B2:B11, on its own. This is called spilling. You don’t need to drag it down or press Ctrl+Shift+Enter.
The first four dates return 2025, and the remaining six return 2026. Notice that 30-Jun-2025 returns 2025, while 01-Jul-2025 returns 2026.
For Excel 2019 and earlier, enter this formula in C2, under “Fiscal Year (Excel 2019 and Earlier)”, and copy it down to C11:
=YEAR(A2)+(MONTH(A2)>=7)
This gives you the same fiscal years, one row at a time.
In case you see a date in cell B2, you can change the cell format to show the numbers (instead of the date). Here are the steps to do this:
- Select the cells that have the result that is showing up as a date instead of numbers.
- Click the Home tab.
- Click on the number-format dropdown in the Number group.
- Select the ‘General’ option.
Now, let me explain how this formula works.
The YEAR function extracts the year from each date. For 05-Sep-2025, that gives us 2025.
The MONTH function extracts the month number. The comparison (MONTH(A2)>=7) checks whether that month is greater than or equal to 7.
If the month is July or later, the comparison returns TRUE. In this addition, Excel treats TRUE as 1, so the formula adds 1 to the year.
If the month is before July, it returns FALSE. Excel treats FALSE as 0, so the year stays the same.
The spilling formula makes this same calculation for every date in A2:A11.
But what if the fiscal year is not between July and June? What if it starts in some other month, say Apr-Mar?
In that case, change 7 to the start month number. For an April-to-March fiscal year, use this formula in B2:
=YEAR(A2:A11)+(MONTH(A2:A11)>=4)
Here are some common fiscal year start months and the number to use in the formula.
| Country / body | Fiscal year | Month number |
|---|---|---|
| US federal government | 1 October to 30 September | 10 |
| India | 1 April to 31 March | 4 |
| UK government | 1 April to 31 March | 4 |
| Australia | 1 July to 30 June | 7 |
Companies often pick their own fiscal year, so check which start month yours uses.
The UK personal tax year starts on 6 April, rather than 1 April. I’ll cover starts like this in the mid-month section below.
These formulas name the fiscal year by the year it ends, the usual convention. For example, the US federal FY2026 ends on 30 September 2026.
If your company names a July-to-June fiscal year by the year it starts, use this formula in B2. It returns one less than the end-year formula:
=YEAR(A2:A11)-(MONTH(A2:A11)<7)
In case your fiscal year and calendar year are the same (Jan-Dec), the fiscal year is just the year in the date, which you can extract using YEAR.
If your dates come in through Power Query, you can calculate the fiscal year in Power Query too.
Also read: How to Calculate Years of Service in Excel
Get Fiscal Year from Date with Different Start Months
In the above example, I assumed that the fiscal period for all the transactions was July to June.
But what if we have different fiscal periods for different transactions?
On the “Start Month per Row” sheet, I have dates in A2:A11 and FY Start Month numbers in B2:B11. I want each date’s fiscal year in column C.
Column B uses month numbers from 1 to 12. If you have names such as “July”, you can convert a month name to a number first.
Enter this spilling formula in C2, under “Fiscal Year”:
=YEAR(A2:A11)+IF(B2:B11<>1,MONTH(A2:A11)>=B2:B11,0)
In Excel for Microsoft 365, Excel 2024, and Excel 2021, the formula spills into C2:C11 on its own.
For example, 14-Feb-2025 with an April start returns 2025. The date 11-Aug-2025 with a July start returns 2026.
The date 03-Oct-2025 has a January start, so it returns 2025. The date 01-Apr-2026 has an April start, so it returns 2027.
For Excel 2019 and earlier, enter this formula in C2 and copy it down to C11:
=YEAR(A2)+IF(B2<>1,MONTH(A2)>=B2,0)
The YEAR function extracts the calendar year. The IF part determines whether to add 1 or keep that year unchanged:
IF(B2<>1,MONTH(A2)>=B2,0)
- If the fiscal year starts in a month other than January, the formula checks whether the date’s month is greater than or equal to the start month. It adds 1 when this is TRUE, and adds 0 when the date’s month is earlier.
- If the fiscal year starts in January (month 1), the IF function returns 0. The fiscal year is the same as the year extracted from the date.
A blank start-month cell is treated as “add 1” and returns the next year, so fill every row of column B.
If you want a range like 2024-2025, use the formula below. This is useful when your data mixes fiscal calendars, such as different companies’ fiscal years.
Enter this formula in D2, under “Fiscal Year Range”. In Excel for Microsoft 365, Excel 2024, and Excel 2021, it spills into D2:D11:
=LET(fy,YEAR(A2:A11)+IF(B2:B11<>1,MONTH(A2:A11)>=B2:B11,0),IF(B2:B11=1,TEXT(fy,"0"),fy-1&"-"&fy))
Rows 5 (03-Oct-2025) and 11 (16-Jul-2026) have a January start, so they show 2025 and 2026. A January-December fiscal year has no two-year range.
LET names the fiscal year fy, using the same calculation as the C2 formula.
IF checks the start month. For January (month 1), it returns the year alone. Otherwise, it joins the year before, a dash, and the fiscal year.
TEXT(fy,"0") turns the January-start year into text, so every result in the column is text and lines up the same way.
For Excel 2019 and earlier, enter this formula in D2 and copy it down to D11:
=IF(B2=1,TEXT(YEAR(A2),"0"),YEAR(A2)+(MONTH(A2)>=B2)-1&"-"&YEAR(A2)+(MONTH(A2)>=B2))
Also read: Calculate Quarter from Date in Excel
Follow along with the example file
Fiscal Year from Date Excel.xlsx
Show the Fiscal Year as a Range or Label
In most cases, a fiscal year is written in the format 2024-2025 (which indicates that the fiscal year started in 2024 and ends in 2025).
Many teams, for example those using the Indian financial year from April to March, write it as 2025-26 or FY26 instead.
On the “Fiscal Year Labels” sheet, I have transaction dates in A2:A11. I want to show their April-to-March fiscal years as ranges and short labels.
For the full range, enter this financial year formula in B2, under “Fiscal Year (April Start)”:
=LET(fy,YEAR(A2:A11)+(MONTH(A2:A11)>=4),fy-1&"-"&fy)
The results spill down to B11 automatically in Excel for Microsoft 365, Excel 2024, and Excel 2021.
The first three dates return 2024-2025, the next five return 2025-2026, and the last two return 2026-2027.
The LET function names the fiscal year fy once, so we can reuse it without repeating the calculation.
The & joins the year before the fiscal year, a dash, and the fiscal year itself. For a fiscal year ending in 2026, it joins 2025, “-“, and 2026.
To get a shorter range like 2025-26, enter this formula in C2, under “Short Format”:
=LET(fy,YEAR(A2:A11)+(MONTH(A2:A11)>=4),fy-1&"-"&RIGHT(fy,2))
RIGHT keeps only the last two digits of the ending year. C2:C4 show 2024-25, C5:C9 show 2025-26, and C10:C11 show 2026-27.
If you prefer labels like FY26, enter this formula in D2, under “FY Label”:
="FY"&RIGHT(YEAR(A2:A11)+(MONTH(A2:A11)>=4),2)
This joins “FY” with the last two digits of the ending year. D2:D4 return FY25, D5:D9 return FY26, and D10:D11 return FY27.
For Excel 2019 and earlier, which don’t support LET or spilling, use this full-range formula in B2 and copy it down to B11:
=YEAR(A2)+(MONTH(A2)>=4)-1&"-"&YEAR(A2)+(MONTH(A2)>=4)
It repeats the fiscal year calculation on each side of the dash to give you the same full range.
A January-December fiscal year has no range label spanning two years. It is just the year, such as 2025.
Fiscal Years That Start Mid-Month
If your fiscal year starts on a day other than the 1st, compare the whole date with the start date, rather than checking only its month.
For example, the UK personal tax year runs from 6 April to 5 April. A date like 3 April is in April but still belongs to the old tax year.
A formula that checks only whether the month is April or later would put that date in the wrong year.
On the “Mid-Month Start” sheet, I have transaction dates in A2:A11. I want their fiscal year labels in column B, using 6 April as the start date.
Enter this spilling formula in B2, under “UK Tax Year (6 Apr to 5 Apr)”:
=LET(ty,YEAR(A2:A11)+(A2:A11>=DATE(YEAR(A2:A11),4,6)),ty-1&"-"&RIGHT(ty,2))
With Excel for Microsoft 365, Excel 2024, or Excel 2021, one formula spills the labels into B2:B11.
The date 05-Apr-2025 returns 2024-25, while 06-Apr-2025 returns 2025-26. Similarly, 05-Apr-2026 returns 2025-26, and 06-Apr-2026 returns 2026-27.
How this formula works:
DATE(YEAR(A2:A11),4,6)builds 6 April of the same calendar year as each transaction date.- The comparison checks whether each transaction date is on or after that date. TRUE adds 1 to the calendar year; FALSE adds 0.
- LET names the ending year
ty. The formula joins the previous year, a dash, and the last two digits oftyto create the label.
To use another start date, change the 4 (month) and 6 (day) inside the DATE function. The rest of the formula works the same way.
Replace “-” with “/” in the formula to get a label such as 2025/26, a format common in UK tax documents.
For Excel 2019 and earlier, enter this formula in B2 and copy it down to B11:
=YEAR(A2)-(A2<DATE(YEAR(A2),4,6))&"-"&RIGHT(YEAR(A2)+(A2>=DATE(YEAR(A2),4,6)),2)
The first part gets the starting year, and the second gets the last two digits of the ending year. It gives you the same labels as the spilling formula.
Also read: How to Group Dates in Pivot Tables in Excel (by Years, Months, Weeks)
Fiscal Year vs Financial Year
The terms “fiscal year” and “financial year” are often used interchangeably, and both refer to a 12-month period used for accounting purposes by businesses and governments.
In the context of companies in the United States, “fiscal year” and “financial year” mean the same thing and refer to the 12 consecutive months used as an accounting period.
The key difference, if any, might be in the usage based on the entity being referred to.
For example, “fiscal year” is more commonly used when discussing governmental accounting and budgeting.
Both “fiscal year” and “financial year” are used when referring to businesses, but the term might be chosen based on regional or industry-specific practices.
I’ve covered formulas for a fixed fiscal year start month and a different start month per date, along with range labels and years that start mid-month.
I hope this article was useful for you. If you know of any other method that can be used to do this, do let me know in the comments section.
Other Excel articles you may also like:
