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 and some variations to use that formula.
Click here to download the example file and follow along
Contents
Get Fiscal Year From Date (same Fiscal Period)
Below is a data set where I have transaction dates in column A, and I want to get the fiscal year value for each date in column B.
For this example, I will assume that the new fiscal year for all the dates starts in July.
We can’t just extract the Year value of the date to get the fiscal year (as fiscal year is different from calendar year). In our example, since the fiscal year starts in July, any date before July would be considered in the current year’s fiscal period, and any date in or after July would be considered a part of 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.
Here is the formula to get the fiscal year using the date:
=YEAR(A2)+(MONTH(A2)>=7)
Enter this formula in cell B2 and copy it for all the other cells in the column.
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 formatting dropdown in the Number group.
- Select the ‘General’ option.
Now, let me explain how this formula works.
In the above formula, we extract the year value from the date using the YEAR function.
However, since a new fiscal year starts from July onwards, we check whether the Month value in the date is more than or equal to 7 or not.
If the month value is seven or more, (MONTH(A2)>=7) would return TRUE, where TRUE has a value of 1 in Excel. This 1 is then added to the year value we extracted from the date to get the fiscal year value.
If the month value is less than 7, (MONTH(A2)>=7) would return FALSE, which has the value of 0 in Excel. This would then give us the fiscal year value, which is the same as the year in the date.
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, you need to adjust the formula by using a different month number to compare with.
For example, if the fiscal year is from April to March, then a formula would become:
=YEAR(A2)+(MONTH(A2)>=4)
In case your fiscal year and calendar year are the same (i.e., Jan-Dec), Then the fiscal year would be the same as the year in the date (Which you can extract using the YEAR function)
Also read: How to Calculate Years of Service in Excel
Get Fiscal Year From Date (different Fiscal Periods)
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?
For example, I have a dataset below with dates in column A and the month in which the fiscal year starts in column B, and I want to get the Fiscal year value in column C.
The formula below works in this case and gives us the fiscal year value:
=YEAR(A2)+IF(B2<>1, MONTH(A2)>=B2,0)
Enter this formula in cell B2 and copy it for all the other cells in the column.
In the above formula, I extract the year value from the date using the YEAR function.
Now, I need to analyze the start month of the fiscal year to determine whether the year value I’ve extracted remains the fiscal year value or it would be the next year.
This is done by the following part of the formula:
IF(B2<>1, MONTH(A2)>=B2,0)
Here, the above part of the formula checks for two conditions and does the following:
- If the month value in the date is more than that of the start date of the first month of the fiscal year, then I need to add 1 to the Year value that I extracted from the date. And if the month value of the date is less than that of the start date of the first month of the fiscal year, I don’t need to change the Year value extracted from the date.
- In case the fiscal year starts from January itself (which is month 1), The fiscal year would be the same as the year extracted from the date.
Also read: Calculate Quarter from Date in Excel (Easy Formula)
Get Fiscal Year Range from Date
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).
So, if you want to get the result in this format, you can adjust the formula to do this.
Below is the data set where I have transaction dates in column A, the fiscal year start month in column B, and I want to get the fiscal dates in column C.
Here is the formula that would give me the result in the desired format:
=(YEAR(A2)+IF(B2<>1, MONTH(A2)>=B2,1)-1)&"-"&(YEAR(A2)+IF(B2<>1, MONTH(A2)>=B2,0))
Enter this formula in cell B2 and copy it for all the other cells in the column.
The above formula works the same way as covered in the previous section, but since we want a range, it uses the ‘&’ to concatenate the year value, which is one less than the fiscal year value (along with the dash in between).
In this article, I covered some simple formulas to get the fiscal year from a date in Excel.
I’ve shown both scenarios where the starting month of the fiscal year is fixed, and you need to get the fiscal year for a set of dates, or where we have a different fiscal year starting month for each date.
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 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: