Table of Contents
Working with dates in Excel can be tricky. Knowing how to quickly get fiscal year from a given date can be especially useful when you need to prepare financial statements or other documents with fiscal year information. In this article, we will show you step by step how to get fiscal year from a date in Excel. We will also provide you with some useful tips and tricks to make the process easier.
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.