In this post you will learn how to calculate the fiscal quarter from a date in Excel.
Calculating the fiscal quarter for a date –
Many of us work in organizations where the fiscal year does not start in January. Instead,
it starts in October or April or any other month. In these organizations, the fiscal quarters
can’t be calculated in the same way as calendar quarters.
Figure below demonstrates a clever formula for converting a date into a fiscal quarter using
the CHOOSE function. In this example, we’re calculating the fiscal quarters when our fiscal
year starts in April. The formula seen in the Formula bar shows the following:
=CHOOSE(MONTH(B3),4,4,4,1,1,1,2,2,2,3,3,3)
The CHOOSE function returns an answer from a list of choices based on a position num-
ber. If you were to enter the formula =CHOOSE(2, “Gold”, “Silver”, “Bronze”,
“Coupon”), you would get Silver because Silver is the second choice in your list of
choices. Replace the 2 with a 4, and you would get Coupon, the fourth choice.
The CHOOSE function’s first argument is a required index number. This argument is a num-
ber from 1 to as many choices as you list in the next set of arguments. The index number
determines which of the next arguments is returned.
The next 254 arguments (only the first one is required) defines your choices and determines
what is returned when an index number is provided. If the index number is 1, the first
choice is returned. If the index number is 2, the second choice is returned.
The idea here is to use the CHOOSE function to pass a date to a list of quarter numbers:
=CHOOSE(MONTH(B3),4,4,4,1,1,1,2,2,2,3,3,3)The formula shown in cell C3 (see Figure above) tells Excel to use the month number for
the given date and select a quarter that corresponds to that number. In this case, since
the month is January, Excel returns the first choice (January is the first month). The first
choice happens to be a 4. January is in the fourth fiscal quarter.
Let’s say that your company’s fiscal year starts in October instead of April. You can easily
compensate for this by simply adjusting your list of choices to correlate with your fiscal
year’s start month.
=CHOOSE(MONTH(B3),2,2,2,3,3,3,4,4,4,1,1,1)