The most common financial year formula for Google Sheets looks a little like this:
=IF(MONTH(A3) < 7, YEAR(A3) - 1, YEAR(A3))
Where:
=IF(MONTH(date) < financial_year_starting_month, YEAR(date) - 1, YEAR(date))
In Australia, our financial year starts on the 1 July. So that’s what I’ve used in the example.
So in the above example, we are saying that if the current month of the selected date is less than the starting month of the financial year, then display the previous year. Otherwise, display the current year.
This approach relies on the YEAR() and MONTH() functions to get the corresponding values from the selected date.
And this may well be all you need. Just click that first cell in the bottom right corner and drag down all the dates that you need to and your job is done.
But what about financial years that don’t start on the first of the target month like:
- the United Kingdom, 6 April.
- Nepal, 16 July.
- Iran, 21 March.
- Ethiopia, 8 July.
What if you want to build the formula in a single handy cell at the top of your dataset?
What if you want to exclude empty cells?
What if you want to include the end year or the full date range?
Let’s dive into the weeds together and see if we can come up with some better solutions for our financial year formula.
Oh! You can also check out the video tutorial on this topic and follow along with the starter sheet that you can get a copy of here:
Table of Contents
The Video
Financial Years that don’t start on the 1st of the month
Here we need to compare the whole financial year date against the target date.
In our example, we will use the UK’s financial year which starts on 8 July ( Sorry Ethiopia, Nepal and Iran, I still love you, but you know how needy the UK is, so we’ll let em have it, k?).
Okay, you gorgeously damp British folk, let’s dive in using our current example.
=IF(A3 < DATE(YEAR(A3), 4, 6), YEAR(A3) - 1, YEAR(A3))
Here, we are modifying the expression of the IF statement. We are stating that if our selected date is less than the target financial year month and day for the same year as the selected date then subtract a year and display that year. Otherwise, display the same year as the selected date.
=IF(A3 < DATE(YEAR(selected_date), month, date)...
We use the DATE() function to generate the financial year as a date for the current year. This function takes 3 numerical arguments:
- Year
- Month
- Day of the month
Again, you will need to drag this formula down the range, which kinda sucks and is more difficult to maintain.
Apply the Google Sheets Financial Year Formula to a Range
We can take advantage of the ARRAYFORMULA function (Though you could use the MAP lambda function here too) to iterate through each date in our range to provide the financial year for each one.
Let’s take a look using our UK example.
=ARRAYFORMULA( IF(A3:A = "", "", IF(A3:A < DATE(YEAR(A3:A), 4, 6), YEAR(A3:A) - 1, YEAR(A3:A))))
Where:
=ARRAYFORMULA( IF(range = "", "", IF(range < DATE(YEAR(range), month, day), YEAR(range) - 1, YEAR(range))))
First, we wanted to handle for any blank spaces so that weird 1899 does not appear. We do this with the initial IF functions stating that if the cell is empty, display an empty cell. Otherwise, complete the financial year formula.
Next, we will need to change our static cell reference to our range of dates.
… and we’re done. Easy!
This is my preferred approach to displaying the financial year. It makes it really easy to sort ranges and create charts. However, if you want a little more information, read on.
Adding an End Year
In this example, we will continue with our UK financial year and include the end year.
=ARRAYFORMULA(
IF(
A3:A = "",
"",
IF(
A3:A < DATE(YEAR(A3:A), 4, 6),
YEAR(A3:A) - 1 &"/"& YEAR(A3:A),
YEAR(A3:A) &"/"& YEAR(A3:A) + 1)
)
)
Where:
=ARRAYFORMULA(
IF(
range = "",
"",
IF(
range < DATE(YEAR(range), fin_yr_month, fin_yr_day),
YEAR(range) - 1 &"/"& YEAR(range),
YEAR(range) &"/"& YEAR(range) + 1)
)
)
Here you can see that we have included an ampersand, double quotation and forward slash combo (&"/"&). This is used to create a text item containing the forward slash between our two years.
In our first IF condition, we add the year minus 1 on one side and the current year on the other.
Then in the second IF condition, we display the current year on the left and the following year on the right.
Adding the End Year’s Last Two Digits
Maybe having the whole end year is overkill. Perhaps we only want to display the last 2 digits of the end year.
Let’s modify our example to just show the start financial year and then the last bit of the end of the financial year.
Here is the formula:
=ARRAYFORMULA(
IF(
A3:A = "",
"",
IF(
A3:A < DATE(YEAR(A3:A), 4, 6),
YEAR(A3:A) - 1 &"/"& RIGHT(YEAR(A3:A), 2),
YEAR(A3:A) &"/"& RIGHT(YEAR(A3:A) + 1, 2)
)
)
)
Where:
=ARRAYFORMULA(
IF(
range = "",
"",
IF(
range < DATE(YEAR(range), fin_year_month, fin_year_day),
YEAR(range) - 1 &"/"& RIGHT(YEAR(range), 2),
YEAR(range) &"/"& RIGHT(YEAR(range) + 1, 2)
)
)
)
Here we modify the last year with the RIGHT function that takes a value as the first argument, then a number of characters to capture from the right for the second argument.
For us, that means we are capturing the last two digits of the end year.
Adding the Full Financial Year Start and End Date into the Google Sheets Formula
In our final example, we will go the whole hog and add the complete financial year start date and end date.
We will again separate the two dates by a forward slash and this time add two spaces on either side to make the dates look clearer.
Here is what our dates would look like:
=ARRAYFORMULA(
IF(
A3:A = "",
"",
IF(
A3:A < DATE(YEAR(A3:A), 4, 6),
TEXT(DATE(YEAR(A3:A) -1, 4, 6), "YYYY-MM-DD")
&" / "&
TEXT(DATE(YEAR(A3:A), 4, 6) - 1, "YYYY-MM-DD"),
TEXT(DATE(YEAR(A3:A), 4, 6),"YYYY-MM-DD")
&" / "&
TEXT(DATE(YEAR(A3:A) + 1, 4, 6) - 1, "YYYY-MM-DD")
)
)
)
In this example, you will have noticed we are using a weird TEXT, DATE, YEAR, month and day combo.
We need to add the TEXT function otherwise we will get the date as a strange Google Sheets value instead of an actual date.
The TEXT function takes two arguments:
- Text or number to convert – in our case, a date.
- The format that you want to convert to.
Here is our first example again:
TEXT(DATE(YEAR(A3:A) -1, 4, 6), "YYYY-MM-DD")
Of course, you don’t have to do it in the uber-nerd year-month-day format. Here are some other options (examples are for 6 April 2024):
"MM-DD-YYYY": e.g. 04-06-2024"DD/MM/YY": e.g. 06/04/24"DD MMM YY": e.g. 06 Apr 24
Check out the docs on the TEXT function for more.
Conclusion
I use these financial year formulas often when creating bookkeeping Spreadsheets for my clients and my own business. I have also used variants of these formulas for seasonal years like tour seasons.
I’d be curious to hear how you would use these functions or a variant of them in your own projects.
If you have enjoyed this you might also like:
Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.
~Yagi