How to Calculate Fiscal Year from Date in Excel (Formulas)
How to Calculate Fiscal Year from Date in Excel (Formulas). In a recent project, I encountered the task of categorizing dates into their respective fiscal years (also known as financial years) using Excel. Although Excel lacks a dedicated function for this purpose, it can be accomplished efficiently through a combination of functions.
This article demonstrates how to calculate the fiscal year from a given date in Excel using a formula and explores variations of that formula.
Obtaining Fiscal Year from Date (Same Fiscal Period)
Consider the dataset below with transaction dates in column A and the corresponding fiscal year values desired in column B.
Assuming that the new fiscal year begins in July, a formula can be employed to achieve this:
=YEAR(A2) + (MONTH(A2) >= 7)
Apply this formula in cell B2 and copy it for the entire column.
In case the result in cell B2 displays as a date, adjust the cell format to show only numbers:
- Select cells displaying dates.
- Navigate to the Home tab.
- Click on the formatting dropdown in the Number group.
- Choose the ‘General’ option.
Now, let’s break down the formula:
- Extract the year using the YEAR function.
- Check if the month in the date is greater than or equal to 7 (July onwards).
- If true, add 1 to the extracted year to obtain the fiscal year.
Adjust the formula if the fiscal year starts in a different month, like April:
=YEAR(A2) + (MONTH(A2) >= 4)
Fiscal Year Calculation with Different Fiscal Periods
If fiscal periods vary for different transactions, the formula can be adapted accordingly. Consider the dataset with dates in column A, fiscal year start months in column B, and the desired fiscal year values in column C.
The formula for this scenario is:
=YEAR(A2) + IF(B2 <> 1, MONTH(A2) >= B2, 0)
Apply this formula in cell C2 and copy it for the entire column.
Here’s how this formula works:
- Extract the year using the YEAR function.
- Determine if the month in the date is greater than the fiscal year’s starting month.
- If true, add 1 to the extracted year; otherwise, keep the extracted year.
Calculate Fiscal Year Range from Date
To represent Calculate fiscal years in the format “2024-2025,” adjust the formula as follows:
=(YEAR(A2) + IF(B2 <> 1, MONTH(A2) >= B2, 1) - 1) & "-" & (YEAR(A2) + IF(B2 <> 1, MONTH(A2) >= B2, 0))
Apply this formula in cell C2 and copy it for the entire column.
This formula works similarly to the previous one but concatenates the year values with a dash to represent a range.
Fiscal Year vs Financial Year
The terms “fiscal year” and “financial year” are often used interchangeably, both denoting a 12-month accounting period for businesses and governments. 
While the usage may vary based on regional or industry-specific practices, the terms are generally interchangeable in the context of businesses.
How to Calculate Fiscal Year from Date in Excel (Formulas). I hope this article proves helpful. If you have alternative methods or suggestions, please share them in the comments section.
