Find fiscal year in excel

Summary

In business each transaction is recorded on the date it took place. With this entry the companies are able to determine the annual turnover that took place in their company in the last year. The thing is that companies when referring to years, usually mean financial years or fiscal which do not have to start from 1/1.

Find fiscal year

Each company has its own date that determines the fiscal year. For example, Excelixis company has a starting point of fiscal year April 1st. 

Below you see the table with some transactions and in Column C you see the fiscal year that is calculated with the help of a formula.

All date starting from 1/4/2022 belong to fiscal year 2023.

Fiscal year 2022 started from 4/1/21 until 31/3/2022

The formula we use to find fiscal year:

Systax and formula analysis

=SUM(YEAR(B5);MONTH(B5)>3))

We use SUM function which returns the summary of parameteres inside the parenthesis.

The first parameter is the year of date

=YEAR(B5)

That will result 2022

Second parameter inside SUM is a comparison of the month, with the help of MONTH(B5) function, if it is greater that 3 which returns the month of the date in B5. It compares it if it is greater than 3.

If the logical test is true =MONTH(B5)>3 the it converts to 1 so it adds up to sum, else it will be false so the value will be 0 and the year is not affected.

So, SUM will have =SUM(2022;0) // Returns 2022+0=2022 for all the values where the month is NOT greater or equal to 3 and =SUM(2022;1) // returns 2022+1=2023 for all months where the transaction month is greater than 3.