How to Convert a Date into Financial Months in Excel

In some companies, financial or budget months do not start on the first day or end on the 30th or 31st day of the calendar month. Instead, they may be defined by other dates. For example, each financial month in a company may begin on the 21st day of a calendar month and end on the 20th day of the next month.



Formula to Convert a Date into Budget Months

For such cases, it's important to define the financial month based on a standard date. The formula below converts a date into a financial month using the functions =EOMONTH() and =TEXT(). In this example, the formula calculates the budget month starting on the 21st day of the calendar month and ending on the 20th day of the next month.

=TEXT(EOMONTH(A3-$E$3,1),"mmm")
EOMONTH.

In the formula's arguments, instead of hardcoding the end day of the budget month, it’s better to reference a cell where you can specify the desired day. For example, in cell D3, we input the calendar day the financial month starts. In cell E3, using the formula =D3-1, we calculate the last day of the financial month and pass it to the formula for further use as an argument in the calculation:

Period Setup

Download Example How to Convert Date into Financial Months in Excel download file

In the formula above, the date in cell A3 is first read. Then, by subtracting 20 from it, the date 20 days earlier is calculated. The newly computed date is passed to the first argument of the EOMONTH function to determine the last day of the next month (considering whether it has 30 or 31 days).

The result is then passed to the TEXT function to convert the resulting number into the abbreviated names of the months in the year.