In this post, you will learn how to calculate the fiscal month from a date in Excel.
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 below 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")
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")