Part II: Working with Formulas and Functions
The formula shown in cell C3 (see Figure 12.12) 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)
Returning a fiscal month from a date
In some organizations, the operationally recognized months don’t start on the 1st and end
on the 30th or 31st. Instead, they have specific days marking the beginning and end of a
month. For instance, you may work in an organization where each fiscal month starts on
the 21st and ends on the 20th of the next month. In these organizations, it’s important to
be able to translate a standard date into their own fiscal months.
Figure 12.13 demonstrates a formula for converting a date into a fiscal month using the
EOMONTH function in conjunction with the TEXT function. In this example, we’re calculat-
ing the fiscal month where our fiscal month starts on the 21st and ends on the 20th of the
next month. The formula in cell C3 shows the following:
=TEXT(EOMONTH(B3-20,1),"mmm")
FIGURE 12.13
Calculating fiscal months
In this formula, we’re first taking our date (in B3) and going back 20 days by subtracting 20.
Then we are using that new date in the EOMONTH function to get the last day next month:
EOMONTH(B3-20,1)
We then wrap that in a TEXT function to format the resulting date into a three-letter
month name:
TEXT(EOMONTH(B3-20,1),"mmm")
