A fiscal quarter is a three-month period that a company or organization uses to report its financial performance and plan its budget. Fiscal quarters are usually aligned with the calendar year, but some organizations may have different fiscal years that start and end in different months. For example, the U.S. federal government’s fiscal year starts in October and ends in September.
In this article, we will show you how to calculate the fiscal quarter for a date in Excel, using different formulas and methods. We will also explain the basic theory behind the calculations and provide a detailed example with real data.
To calculate the fiscal quarter for a date, we need to know two things: the month of the date and the starting month of the fiscal year. The month of the date can be easily obtained by using the MONTH function in Excel, which returns a number from 1 to 12 corresponding to the month of a given date. For example, =MONTH(“2024-02-02”) returns 2, which means February.
The starting month of the fiscal year can be either given as a parameter or assumed to be January by default. Depending on the starting month, the fiscal quarters can be divided as follows:
Table
| Starting Month | Q1 | Q2 | Q3 | Q4 |
|---|---|---|---|---|
| January | Jan-Mar | Apr-Jun | Jul-Sep | Oct-Dec |
| April | Apr-Jun | Jul-Sep | Oct-Dec | Jan-Mar |
| July | Jul-Sep | Oct-Dec | Jan-Mar | Apr-Jun |
| October | Oct-Dec | Jan-Mar | Apr-Jun | Jul-Sep |
Once we have the month of the date and the starting month of the fiscal year, we can use a formula to map the month to the corresponding quarter number, from 1 to 4. There are different ways to write such a formula, which we will discuss in the next section.
Procedures
Using the CHOOSE Function
One way to calculate the fiscal quarter for a date is to use the CHOOSE function in Excel. The CHOOSE function returns a value from a list of choices based on a position number. For example, =CHOOSE(2,“small”,“medium”,“large”) returns “medium”, because “medium” is the second choice in the list.
We can use the CHOOSE function to map the month of the date to the quarter number, by providing 12 choices that correspond to the fiscal year structure. For example, if the fiscal year starts in April, we can use the following formula:
=CHOOSE(MONTH(date),4,4,4,1,1,1,2,2,2,3,3,3)
This formula returns a number from 1 to 4 that represents the fiscal quarter for the given date. The logic behind the formula is that the MONTH function returns the month number of the date, which is then used as the position number for the CHOOSE function. The CHOOSE function then returns the quarter number that matches the position number. For example, if the date is “2024-02-02”, the formula returns 4, because February is the 2nd month and the 2nd choice in the list is 4.
We can also add a “Q” and a year value to the formula, by using concatenation. For example:
=“Q”&CHOOSE(MONTH(date),4,4,4,1,1,1,2,2,2,3,3,3)&” – “&YEAR(date)
This formula returns a text value like “Q4 – 2024”, which shows the fiscal quarter and the year for the given date.
If the fiscal year starts in a different month, we just need to adjust the order of the choices in the CHOOSE function accordingly. For example, if the fiscal year starts in October, we can use the following formula:
=CHOOSE(MONTH(date),2,2,2,3,3,3,4,4,4,1,1,1)
This formula returns the correct quarter number for a fiscal year that starts in October and ends in September.
Using the ROUNDUP and MOD Functions
Another way to calculate the fiscal quarter for a date is to use the ROUNDUP and MOD functions in Excel. The ROUNDUP function rounds a number up to a specified number of digits. The MOD function returns the remainder after dividing one number by another. For example, =ROUNDUP(2.5,0) returns 3, and =MOD(7,3) returns 1.
We can use the ROUNDUP and MOD functions to calculate the quarter number based on the month number and the starting month of the fiscal year. For example, if the fiscal year starts in April, we can use the following formula:
=MOD(ROUNDUP((MONTH(date)+9-4)/3,0),4)+1
This formula returns a number from 1 to 4 that represents the fiscal quarter for the given date. The logic behind the formula is that we first add 9 to the month number of the date, then subtract the starting month of the fiscal year (4 in this case), then divide the result by 3 and round it up to get the quarter number based on a calendar year that starts in January. Then we use the MOD function to adjust the quarter number for the fiscal year that starts in April, by taking the remainder after dividing by 4 and adding 1. For example, if the date is “2024-02-02”, the formula returns 4, because:
- MONTH(“2024-02-02”) returns 2
- 2 + 9 – 4 = 7
- ROUNDUP(7/3,0) = 3
- MOD(3,4) = 3
- 3 + 1 = 4
We can also add a “Q” and a year value to the formula, by using concatenation. For example:
=“Q”&MOD(ROUNDUP((MONTH(date)+9-4)/3,0),4)+1&” – “&YEAR(date)+(MONTH(date)>=4)
This formula returns a text value like “Q4 – 2024”, which shows the fiscal quarter and the year for the given date. The logic behind the formula is that we use the same formula as before to get the quarter number, then we add the year value of the date, and adjust it by adding 1 if the month of the date is greater than or equal to the starting month of the fiscal year (4 in this case).
If the fiscal year starts in a different month, we just need to change the number 4 in the formula to the starting month of the fiscal year. For example, if the fiscal year starts in October, we can use the following formula:
=MOD(ROUNDUP((MONTH(date)+9-10)/3,0),4)+1
This formula returns the correct quarter number for a fiscal year that starts in October and ends in September.
