Chapter 12: Using Formulas with Dates and Times
12
The following formula does the same thing. We’re using the MONTH function to extract the
month number from the given date and the ROUNDUP function to force rounding up.
=ROUNDUP(MONTH(B3)/3,0)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 12.12 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)FIGURE 12.12
Calculating fiscal quarters
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)