Daljit Dhadwal

Many governments and businesses use fiscal years that are different than the calendar year. A fiscal year that runs from April 1 to March 31 of the following year is quite common. In this post, I’ll demonstrate how to create an Excel function that will tell you which fiscal year any date belongs to.

Let’s assume the fiscal year runs from April to March. Here are some examples of what we want to do:

Here is the formula in cell B2 that will convert the date in cell A2 into a fiscal year:

=IF( MONTH(A2)<=3, "FY" & (YEAR(A2)-1) & "/" & RIGHT(YEAR(A2),2) , "FY" & YEAR(A2) & "/" & RIGHT((YEAR(A2)+1),2) )

If you’re interested in how this formula works, please read on.

We want to implement the following two rules:

• Rule 1: If the month is between April and December then the fiscal year begins with the letters “FY” and then the current calendar year and then a forward “/” and then the last two digits of the following calendar year.
• Rule 2: If the month is between January and March then the fiscal year begins with the letters “FY” and then the previous calendar year and then a forward “/” and then the last two digits of the current calendar year.

We’re going to use the following Excel functions: &, IF, MONTH, YEAR, and RIGHT

Rule 1:
If the month is between April and December then the fiscal year begins with the letters “FY” and then the current calendar year and then a forward “/” and then the last two digits of the next calendar year.

Here is the pseudocode:

“FY” + [Calendar Year] + “/” + last two digits of [Calendar Year+1]

Here is the Excel formula (assuming the date is in cell A2):
= "FY" & YEAR(A2) & "/" & RIGHT((YEAR(A2)+1),2)

Rule 2:
If the month is between January and March then the fiscal year begins with the letters “FY” and then the previous calendar year and then a forward “/” and then the last two digits of the current calendar year.

Here is the pseudocode:

“FY” + [Calendar Year-1] + “/” + last two digits of [Calendar Year]

Here is the Excel formula (assuming the date is in cell A2):
= "FY" & (YEAR(A2)-1) & "/" & RIGHT(YEAR(A2),2)

Now we can take the two rules and put them in an IF statement:

Putting the two rules together:
If the month is January, February, or March then use rule 2 otherwise use rule 1

Here is the pseudocode:

If month less than or equal to 3 then use rule 2 ELSE use rule 1

Here is the final  Excel formula (assuming the date is in cell A2):
=IF( MONTH(A2)<=3, "FY" & (YEAR(A2)-1) & "/" & RIGHT(YEAR(A2),2) , "FY" & YEAR(A2) & "/" & RIGHT((YEAR(A2)+1),2) )