We can get a fiscal year from a date in excel by using YEAR() and MONTH() functions. By using some logic and utilizing these mentioned functions, we can achieve the desired output. Here is a formula we are going to use to get a fiscal year from date.
We will be using the above-mentioned formula to get the fiscal year from date. But first, let us give you an actual example and then we will explain the working of the formula.
How to use the Formula to get a fiscal year from a date?
Figure 1. Example 1 to get a fiscal year from the date
In this example we created a small table containing a date, starting the month of a fiscal year and a result column. Applying the above-mentioned formula will tell us about the fiscal year in the result column. Let’s see how we can do that.
- Select any column, where you want to display the fiscal year of a given date. In this case, we selected H6.
- Go in the function box and type
- Give the arguments i.e. the date and startmonth. For H6 column we gave F6 and G6 as date and startmonth respectively. The result came out 2014.
We are considering the cells F6 and G6 from above example. The YEAR(date) function will give you the year from F6 cell i.e. 2014. Now, using MONTH(date), we will get 1 from G6 cell. After this, the logical operation will be performed. i.e (MONTH(date) >= startmonth). This condition will become false as 1 is less than 7. And in excel for false expression, 0 gets returned. Now, 0 will get added in YEAR(date). And we will get the fiscal year from date, which turned out to be 2014 for the given example date.