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 in which each fiscal month starts on the 21st and ends on the 20th of the next month.
In such an organization, it’s important to be able to translate a standard date into that organization’s own fiscal months.
The figure demonstrates a formula for converting a date into a fiscal month using the EOMONTH function in conjunction with the TEXT function. In this example, you calculate the fiscal month that starts on the 21st and ends on the 20th of the next month. The formula that appears in the Formula Bar is the following:
=TEXT(EOMONTH(B3-20,1),"mmm")
In this formula, you first take the date (shown in cell B3) and go back 20 days by subtracting 20. Then you use that new date in the EOMONTH function to get the last day of the next month.
EOMONTH(B3-20,1)
You then wrap the resulting value in a TEXT function in order to format the resulting date serial number into a three-letter month name.
