A fiscal year is a 12-month period used by companies for accounting purposes.
Sometimes a fiscal year is measured from the beginning of January to the end of December, which matches a calendar year.
However, sometimes companies use a different rolling 12-month period as their fiscal year.
For example, sometimes a fiscal year ranges from the beginning of April to the end of March.
To extract a fiscal year from a date in Excel, you can use the following formula:
=IF(MONTH(A2)>3, YEAR(A2), YEAR(A2)-1)
This particular formula extracts the fiscal year from the date in cell A2 and assumes that the fiscal year ends on the last day of March (the third month of the year).
To use a different ending month, simply change the 3 in the formula to a different value.
For example, if the fiscal year ends on the last day of October then use a 10 instead since this is the 10th month of the year.
The following example shows how to use this formula in practice.
Example: Get Fiscal Year from Date in Excel
Suppose we have the following column of dates in Excel:
Let’s assume that this particular company uses a fiscal year that starts on the first day of April and ends on the last day of March.
We can type the following formula into cell B2 to get the fiscal year for the date in cell A2:
=IF(MONTH(A2)>3, YEAR(A2), YEAR(A2)-1)
We can then click and drag this formula down to each remaining cell in column B:
Column B shows the fiscal year for each date in column A.
Each date that is before April 1, 2023 has a fiscal year of 2022 while each date starting on April 1, 2023 or after has a fiscal year of 2023.
How This Formula Works
Recall the formula that we used to get the fiscal year:
=IF(MONTH(A2)>3, YEAR(A2), YEAR(A2)-1)
Here is how this formula works:
The MONTH function extracts the month from a date.
If the month is greater than 3 (i.e. later than March) then the year from cell A2 is returned.
Otherwise, if the month is equal to or less than 3 then the year before the year in cell A2 is returned.
For example, cell A2 has a date of 1/1/2023. Since the month of this date is less than 3, the year 2022 is returned.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Compare Dates Without Time in Excel
How to Use SUMIFS with a Date Range in Excel
How to Filter Dates by Month in Excel
