You’ll often need to find a fiscal year in many industries, such as finance or government. These years often start in April, which may also vary. In this tutorial, you’ll learn how to get the fiscal year from a date in Excel. Because Excel doesn’t provide a built-in function to get a fiscal year, we’ll need to create our own fiscal year formula. If that sounds intimidating, don’t worry!
By the end of this tutorial, you’ll have learned the following:
- How to get the fiscal year from a date in Excel, starting in April or a custom month
- How to get a fiscal year range in Excel
Get the Financial Year From a Date in Excel
How can you get the fiscal year from a date in Excel?
To get the fiscal year from a date in Excel, you can use the following formula =IF(MONTH(A2)>3, YEAR(A2), YEAR(A2)-1). The formula assumes that a fiscal year starts in April and gets the current year if the month is April or later. If not, it gets the previous year.
Let’s take a look at an example of how you can use Excel to get the fiscal year.
Step 1: Load Your Dataset in Excel
In the example above, we use a dataset with 6 different dates ranging from 2020 through 2023. We want to use this data to figure out which fiscal year each date belongs to.
Step 2: Enter the Fiscal Year Formula Into Cell A2
Into cell B2, enter the financial year formula =IF(MONTH(A2)>3, YEAR(A2), YEAR(A2)-1). This will calculate if a month of a date is in April or later. If it is, it returns the year of the date. If not, it returns the previous year.
Does your financial year start in a different month?
If your financial year starts in a different month, such as July, you can simply adjust the month in the first part of the IF() function. For example, a July financial year start would use the formula: =IF(MONTH(A2)>6, YEAR(A2), YEAR(A2)-1).
Step 3: Copy the Formula to All Cells
To copy this formula for all cells, you can use the fill handle (the little crosshairs in the bottom right of a cell). By clicking and dragging the handle down for all cells in the range, Excel will copy the formula. This will get the financial year for all dates in the range of data.
In the following section, you’ll learn how to get the fiscal year range from a date in Excel
Get the Fiscal Year Range from a Date in Excel
In the previous section, you learned how to get the starting financial year from a date. In many cases, however, you’ll want to display the entire fiscal year range from a date. For example, for May 23, 2023, we want Excel to display 2023-2024, rather than just 2023.
Step 1: Load Your Dataset in Excel
In the example above, we use a dataset with 6 different dates ranging from 2020 through 2023. We want to use this data to figure out which fiscal year each date belongs to. We want to get the entire range of the fiscal year, so that Excel returns 2022-2023 for a date in the 2022 fiscal year.
Step 2: Enter the Fiscal Year Formula Into Cell A2
Enter the fiscal year range formula into cell A2: =IF(MONTH(A2)>3, YEAR(A2), YEAR(A2)-1)&”-“&IF(MONTH(A2)>3, YEAR(A2)+1, YEAR(A2)). The function gets the first year of the fiscal year, then concatenates a hyphen and the year following this year to it. Similar to before, you can modify the starting month of the year by changing the 3 to the relevant month.
Step 3: Copy the Formula to All Cells
To copy this formula for all cells, you can use the fill handle (the little crosshairs in the bottom right of a cell). By clicking and dragging the handle down for all cells in the range, Excel will copy the formula. This will copy the formula for the financial year range into all cells.
Conclusion
In this tutorial, you learned how to get the fiscal or financial year in Excel. Being able to work with years that have different start dates is a common occurrence in industries such as finance, accounting, and government. You first learned how to get the fiscal year using a custom financial year formula. The formula allows you to customize the month in which a fiscal year starts. Then, you learned how to get the fiscal year range, meaning that it returns both years that fall into a fiscal year. Similar to before, the formula allowed you to calculate a different month’s start.
Additional Resources
To learn more about related topics, check out the tutorials below: