Many people work in organizations for which the fiscal year does not start in January. Instead, it starts in October, or April, or any other month. In these organizations, the fiscal quarter can’t be calculated in the same way as a calendar quarter is.
The figure demonstrates a clever formula for converting a date into a fiscal quarter using the CHOOSE function. In this example, you calculate the fiscal quarters when the fiscal year starts in April. The formula you see in the Formula Bar is as follows:
=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 number. 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 number from 1 to as many choices you list in the next set of arguments. Index number determines which of the next arguments is returned.
The next 254 arguments (only the first one is required) define your choices and determine 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 3, the third 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 tells Excel to use the Month number for the given date and select a quarter that corresponds to that number. In this case, because 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.
Say that your company’s fiscal year starts in October instead of April. You can easily compensate for this fact by simply adjusting your list of choices to correlate with your fiscal year’s start month. Notice how the tenth choice in the following formula is a 1. This would mean that October falls in the first fiscal quarter.
=CHOOSE(MONTH(B3),2,2,2,3,3,3,4,4,4,1,1,1)