In some organizations, the fiscal year does not start from January and end in December, but rather follows a different cycle, such as July to June, or April to March. In such cases, it is useful to be able to convert a standard date into the corresponding fiscal month, which may differ from the calendar month.
In this article, we will show you how to use Excel formulas to return a fiscal month from a date, based on the start month of the fiscal year. We will also provide some examples and alternative approaches to achieve the same result.
The basic idea behind returning a fiscal month from a date is to use the CHOOSE function, which returns a value from a list of values based on a given index number. The index number is the month of the date, and the list of values is the sequence of fiscal months, starting from the first fiscal month.
For example, if the fiscal year starts from July, then the sequence of fiscal months is {7,8,9,10,11,12,1,2,3,4,5,6}, where 7 is the first fiscal month, 8 is the second, and so on. If the date is May 15, 2023, then the month of the date is 5, and the corresponding fiscal month is the fifth value in the sequence, which is 11.
The generic formula to return a fiscal month from a date is:
=CHOOSE (MONTH (Date),first,second,third,fourth,fifth,sixth,seventh,eighth,ninth,tenth,eleventh,twelfth)
Where Date is the date that you want to find the fiscal month for, and first, second, … , twelfth are the numbers of the fiscal months, starting from the first fiscal month.
Procedures
To use the formula to return a fiscal month from a date, follow these steps:
- Identify the start month of the fiscal year. For example, if the fiscal year starts from July, then the start month is 7.
- Create a sequence of fiscal months, starting from the start month and ending with the month before the start month. For example, if the start month is 7, then the sequence is {7,8,9,10,11,12,1,2,3,4,5,6}.
- Enter the date that you want to find the fiscal month for in a cell, such as A2.
- In another cell, such as B2, enter the formula =CHOOSE (MONTH (A2),7,8,9,10,11,12,1,2,3,4,5,6), where 7,8,9,10,11,12,1,2,3,4,5,6 are the sequence of fiscal months that you created in step 2.
- Press Enter to get the result. The formula will return a numeric value from 1 to 12, representing the fiscal month of the date.
- If you want to format the result as a text value, such as “Jul” or “July”, you can wrap the formula in a TEXT function, such as =TEXT (CHOOSE (MONTH (A2),7,8,9,10,11,12,1,2,3,4,5,6),“mmm”) or =TEXT (CHOOSE (MONTH (A2),7,8,9,10,11,12,1,2,3,4,5,6),“mmmm”).
- If you have a list of dates that you want to find the fiscal months for, you can copy and paste the formula to the adjacent cells, or use the fill handle to drag the formula down.
Explanation
The formula to return a fiscal month from a date works by using two Excel functions: the MONTH function and the CHOOSE function.
The MONTH function returns the month of a given date as a number from 1 to 12. For example, MONTH (“15-May-2023”) returns 5, which is the month of May.
The CHOOSE function returns a value from a list of values based on a given index number. The syntax of the CHOOSE function is:
=CHOOSE (index_num,value1,value2,…)
Where index_num is a number between 1 and 254, or a cell reference or formula that returns such a number, and value1, value2, … are the values that you want to choose from. The CHOOSE function will return the value that corresponds to the index_num. For example, CHOOSE (3,“Red”,“Green”,“Blue”,“Yellow”) returns “Blue”, which is the third value in the list.
By combining the MONTH function and the CHOOSE function, we can create a formula that returns a fiscal month from a date. The formula is:
=CHOOSE (MONTH (Date),first,second,third,fourth,fifth,sixth,seventh,eighth,ninth,tenth,eleventh,twelfth)
Where Date is the date that you want to find the fiscal month for, and first, second, … , twelfth are the numbers of the fiscal months, starting from the first fiscal month.
The formula works as follows:
- The MONTH function returns the month of the date as a number from 1 to 12. This number becomes the index_num for the CHOOSE function.
- The CHOOSE function returns the value from the list of fiscal months that corresponds to the index_num. The list of fiscal months is created by entering the numbers of the fiscal months, starting from the first fiscal month and ending with the month before the first fiscal month. For example, if the first fiscal month is July, then the list is {7,8,9,10,11,12,1,2,3,4,5,6}.
- The result is a numeric value from 1 to 12, representing the fiscal month of the date.
Example
To illustrate how the formula works, let’s use some real data from a company that has a fiscal year starting from July and ending in June. The company has a table of sales data, with the transaction dates in column A and the sales amounts in column B. The table looks like this:
| Date | Sales |
|---|---|
| 15-May-2023 | $500 |
| 30-Jun-2023 | $600 |
| 01-Jul-2023 | $700 |
| 15-Aug-2023 | $800 |
| 30-Sep-2023 | $900 |
The company wants to find the fiscal month for each transaction date, and add it as a new column in the table. To do this, the company can use the formula =CHOOSE (MONTH (A2),7,8,9,10,11,12,1,2,3,4,5,6) in cell C2, and copy it down to the other cells in column C. The result is:
| Date | Sales | Fiscal Month |
|---|---|---|
| 15-May-2023 | $500 | 11 |
| 30-Jun-2023 | $600 | 12 |
| 01-Jul-2023 | $700 | 1 |
| 15-Aug-2023 | $800 | 2 |
| 30-Sep-2023 | $900 | 3 |
The formula returns the correct fiscal month for each date, based on the start month of the fiscal year, which is July. For example, for the date 15-May-2023, the formula returns 11, which is the fiscal month of May, since the fiscal year starts from July.
Alternative Approaches
There are some alternative ways to return a fiscal month from a date in Excel, without using the CHOOSE function. Here are some examples:
- Using the IF function: You can use the IF function to check whether the month of the date is greater than or equal to the start month of the fiscal year, and then return the appropriate fiscal month. For example, if the fiscal year starts from July, then the formula is:
=IF (MONTH (A2)>=7,MONTH (A2)-6,MONTH (A2)+6)
This formula works by subtracting 6 from the month of the date if it is greater than or equal to 7, or adding 6 to the month of the date if it is less than 7. For example, for the date 15-May-2023, the formula returns 11, which is the fiscal month of May.
- Using the MOD function: You can use the MOD function to return the remainder of dividing the month of the date by 12, and then add the start month of the fiscal year minus 1. For example, if the fiscal year starts from July, then the formula is:
=MOD (MONTH (A2),12)+7-1
This formula works by returning the month of the date modulo 12, which is the same as the month of the date if it is less than 12, or 0 if it is 12. Then, it adds 6, which is the start month of the fiscal year minus 1. For example, for the date 15-May-2023, the formula returns 11, which is the fiscal month of May.
