The Excel financial year formula is a handy formula to work out the finanical year in an Excel spreadsheet. Moreover, knowing how to calculate the financial year from a date allows you to analyse data by financial year.
Use the following formula to calculate the financial year in Excel:
=IF(MONTH(A2)<4,YEAR(A2)-1 & "-" & YEAR(A2),YEAR(A2) & "-" & YEAR(A2)+1)
In the above example A2 contains the Date, the financial year formula is B2 and the date is 7th April 2021. The results should be:
For that extra little professional touch you add a right function to the second year in the formula to have only the last 2 digits of the year appear.
=IF(MONTH(A2)<4,YEAR(A2)-1 & "-" & RIGHT(YEAR(A2),2),YEAR(A2) & "-" & RIGHT(YEAR(A2)+1,2))
After you have entered in the formula you can Autofill the formula down. Now you have something you can add to a Pivot Table and see how much money you made during the last financial year.
The above formula works if your company's financial year begins on the 1st of April. However, in the UK the financial year starts on the 6th of April.
This means we will need to add the DATE function to the formula like.
=IF(A2<=DATE(YEAR(A2),4,5),YEAR(A2)-1 & "-" & YEAR(A2),YEAR(A2) & "-" & YEAR(A2)+1)
The way this formula works is very similar to the other financial year formula however we are using the DATE function rather than MONTH to work out the financial year. Basically, if the date in cell A2 is less than April the 5th of the current year, we are in the previous financial year. Otherwise, we're in the next financial year.
Just one final touch to the formula, let's add the RIGHT function to finish it off.
=IF(A2<=DATE(YEAR(A2),4,5),YEAR(A2)-1 & "-" & RIGHT(YEAR(A2),2),YEAR(A2) & "-" & RIGHT(YEAR(A2)+1,2))
Here's how the formula looks in Excel. I place the formula on separate lines for readability. (Alt + Enter)
To find the financial quarter in a year you can use this formula:
="FQ-" & CHOOSE(MONTH(A2),4,4,4,1,1,1,2,2,2,3,3,3)
In the above formula the date is in cell A2. The formula first uses the MONTH function to get the month number from the date. After that the CHOOSE function will count along the list of numbers and give that number as a result. For instance if today's date is 7th April the MONTH is 4. Afterwards, the CHOOSE function will count, starting with the first number along to the fourth number, which is a 1, as seen below.
Finally, we concatenate or join the letters FQ to the start of the function. As a result you can see the financial Qtr.
Now that you have created the financial year formula, you can add the colour to the Pivot Table.
If you haven't already done so covert your data range into a Table (Ctrl + T).
Next, Click on Summarize with PivotTable. You can find this button at the top of the screen.
Drag Financial Year into the Rows area and Received to the Values Area
You can now do the same with financial quarter.
Drag Date beneath Financial Qtr and remove Years and "Hey Presto!". You now have a Pivot Table display Financial Year, Quarters and the month is in the correct order.
There is one more problem to overcome and that is if you remove the financial quarter, you will see that the order of the months revert back to January at the top and December at the bottom. We can use Custom Lists to overcome this problem.
Click in any cell and type Apr.
Autofill down to March.
Next, goto File - Options. Select Advanced from the left and then scroll all the way down to Custom Lists.
Click OK and OK again. (You can delete the original autofilled list from your spreadsheet).
Now, back in your Pivot Table, right click on any month and go to Sort then More Sort Options.
Click the More Options button then in the More Sort Options box uncheck Sort Automatically every time the report is updated and choose the Custom List you imported.
Click OK then in the Sort (Date) box choose Ascending by Date.
Click OK and you now have a financial year sorted by month without quarters. Plus, April is the first month.
In conclusion, the Excel financial year formula is just a piece of the puzzle. It's important you understand how to extract the financial quarter and change the sort order to give you accurate figures to anaylse.
If you haven't already looked at the financial year tutorial then I really encourage you to do so.
This tutorial is one of a series of Accounts Tutorials that you are encouraged to follow. As a result you will learn everything from creating a basic accounts worksheet to a Pivot Table. Also, you will learn many everday Excel hints and tips.