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)
Once you've created the crazy calculation for financial year beginning 6th April, you'll need to work that into your financial month calculation. We can use a couple of methods. One is to split the year into 13 Periods of 28 days. As a word of explanation, this would equate to 13 periods of 28 days and the last period would be known as "Yearday" or Yearday2 in the case of a leap year. (This I think I will cover in another tutorial). Alternatively, you can use the following calculation:
=TEXT(A2,"mmm") & " " & IF(AND(MONTH(A2)<=4,DAY(A2)<6),YEAR(A2)-1 & "/" & RIGHT(YEAR(A2),2),YEAR(A2) & "/" & RIGHT(YEAR(A2)+1,2))
The first part of the 6th April UK tax formula is:
Extracts the abbreviated month name from the date. So, for example, if the date in cell A2 was 03/04/2021 the formula would return Apr.
The next part of the tax year formula looks like:
This is the logical condition. This condition checks to see whether the month "A2" is currently 4 or less AND that the DAY is less than 6. So this condition basically checks that the date is before the 6th of April of any year. If it is, then that date is counted as the previous financial year. If not (6th April onwards) the date is considered part of the next financial year. This can be see from the true part.
YEAR(A2)-1 & "/" & RIGHT(YEAR(A2),2),
So in the above formula, the date would return (2020/21) if the date was 3rd April 2021. This is because the 3rd of April is before the new tax year of the 6th April. Hence, it's included in the previous finanical year calculation.
The false part of the IF function reads:
YEAR(A2) & "/" & RIGHT(YEAR(A2)+1,2)
Which would mean that the date 7th April 2021 would be shown as (2021/22) because the 7th April is after the cut off date for the new tax year. When you're finished you should have a sheet or a table that looks something like this:
So hopefully you're with me so far. You also, hopefully, can see that we've created something that we can use in a Pivot Table. So let's create a Pivot Table from Sales Amount.
Nearly there! However, as you can see the sort order isn't correct. You've got Apr repeating 3 times at the top. That is because I have April figures across three years.
There's no definitive way to solve this issue. I've done a couple of methods. One is to create another Year column so that you can split the data on the Pivot Table like this:
Now the data you are seeing is from 6th April to 5th April. You can double check the figures by selecting them in the orginal data.
You can see, from the picture above, that I've selected the sales between the 1st and the 5th of April 2021. These sales consist of one sale on the 3rd April and 2 sales on the 5th April. If you look on the status bar below you can see that these sales add up to 1464.
Now, have a look at the Pivot Table, created earlier, you can see that the amount for that year is the same. It should be noted that there are no sales from 6th to the 30th April 2020. This issue will be addressed in the next section.
There is one final little snag. Notice that when displaying the month April within the year grouping levels there's no way of knowing whether the April is from the 6th to the 30th or 1st to 5th. So a little adjustment in the formula is required.
=TEXT(A2,"mmm") & IF(AND(MONTH(A2)=4,DAY(A2)<6)," (1st - 5th April)","")
The bold bit that was added, checks to see if the date in the year is before the 6th April. In much the same way as we check the year, we use an AND(), DAY() function to see if the month is equal to 4 and the day is less than 6. If that's the case then we'll have the text (1st - 5th April) appear.
You can now see the difficulty of dealing with various dates. History has had numerous calendars used for dividing time. The above Excel financial tax year calculation is not a be all and end all answer to the issue. However, hopefully the above solution will give you an idea of how you can create your own Excel formula. I mean if you want to get technical and know how many days there are in a year the answer is:
365.242325 ephemeris days which equates to 365 days 5 hours 48 minutes 56.89s.
Hence I think we will be wrestling with this problem for a few centuries more.
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.
A nice simple formula to calculate the Financial year is to use the EOMONTH(A2,9). Then wrap that function in a year function so that the completed function looks like:
Should you want to make it all clean and tidy. That is, having the financial year display as 2021/22 then you can use the following:
=YEAR(EOMONTH(A2,9))-1 & "-" & RIGHT(YEAR(EOMONTH(A2,9)),2)
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.
Sign up to receive our monthly newsletter including special promotions, hints & tips, and the latest Computer Tutoring news!