Convert Dates to FY-Quarter for July-June Financial Year

Do you find yourself constantly needing to convert dates into “year-quarter” format for the July-June financial year? If so, you’re not alone!

This is a problem I often come across. The complication is that the Australian financial year runs from July 1 to June 30. This is the same as the fiscal year as most states in the US, and also an eclectic bunch of other countries including Egypt, Kenya, Pakistan, and New Zealand (for government only).

Luckily, you can use a formula in Google Sheets or Excel to convert any date into the year-quarter format.

There are many more complicated ways to do this, like long-form equations with a string of “If” statements in them, or even Google Apps Script.

But I put together a formula that does it in a neat mathematical way.

Formula for FY-Quarter format for the July-June Financial Year

Here it is:

=right(eomonth(A2,-6),2)&"/"&right(eomonth(A2,-6),2)+1&"-Q"&mod(roundup(month(A2)/3)+1,4)+1

Let’s break this Google Sheets formula down to see how it works.

First, look at the left-most section, right(eomonth(A2,-6),2)&"/"&right(eomonth(A2,-6),2)+1.

This section says “Look at six months ago, and get the 2-letter year number. Then, add a ‘/’, and add that same 2-letter year number, plus one.”

For example, if the date is Jan 2022, then it says “Six months ago was July 2021, so go ’21’, then /, then ’22′”. This returns “21/22” for the month, which is the correct financial year.

Now the right-most part, which is mod(roundup(month(A2)/3)+1,4)+1. To be honest, I got to this through a bit of trial and error. But it accurately returns the quarter in which you are, depending on the month.

One key part of the formula is mod, which returns the modulus of a division. The modulus is the remainder. For example, 5 mod 3 is 2.

The rest of the formula is just glue to pretty it up.

Putting it all together, the formula produces a string in the format “21/22-Q3” (for example) that represents the financial year and quarter of the date you’re converting in “July-June” fiscal year format.

Wrap up

Overall, this formula is a powerful tool for anyone who needs to work with financial data in many states of the US, Kenya, Egypt, and Australia. (Beats me why they have to be different!). Here’s a list of places and their financial years.

With just a few clicks in Google Sheets or Excel, you can easily convert dates into the year-quarter format for the July-June financial year, saving you time and maybe impressing a partner in your firm with some formula wizardry.