Home > Formulas > Fiscal Year

Excel Fiscal Year Calculations

Learn how to calculate the fiscal year and fiscal month for a specific date in Microsoft Excel, using formulas that are based on the Fiscal Year starting date or starting month. Also, follow this link to see my tutorial on how to show fiscal year results in a pivot table

Author: Debra Dalgleish

Calculate Fiscal Year in Excel

Based on the month number in which the fiscal year starts, you can use the Microsoft Excel IF function to calculate the fiscal year for any date.

In the example shown below, information is entered in the green cells on the spreadsheet:

• fiscal year starting month number is entered in cell C4
• a date is entered in cell C6

Fiscal Year Formula

To get the fiscal year for the given date in cell C6, enter the following formula in cell C8:

• =YEAR(C6) + IF(MONTH(C6)>=C4,1,0)

The above formula result shows the fiscal year for the date. There is an explanation below, for how the formula works.

Number Format

The result in cell C8 might change to a Date format, after you enter the formula, or if you recalculate the worksheet. To fix that, follow these steps:

• Select cell C8
• To open the Format Cells dialog box, press the keyboard shortcut, Ctrl+1 (one)
• Go to the Number tab
• In the Category list, click Number
• For Decimal places, select 0 (zero)
• Remove the check mark for "Use 1000 separator"
• Click OK to apply the number format, and to close the dialog box.

How Fiscal Year Formula Works

To figure out the date's fiscal year without an Excel formula, this is the logic we would use:

• If the date's month is before the start of the fiscal year:
  • Fiscal year is the same as the Calendar year
• If the month is on or after the start of the fiscal year:
  • Fiscal year number is Calendar year + 1

To calculate the fiscal year witn an Excel formula, we need 3 pieces of information:

1. Date's year number
2. Date's month number
3. Is month number on or after Fiscal Year start month

1) Get the Year

First, the formula calculates the year for the date entered in cell C6, using the YEAR function.

• =YEAR(C6)

2) Get the Month

Next, the MONTH function gets the month number for the date in cell C6:

• MONTH(C6)

3) Compare Month Numbers

After that, the formula compares the month number for cell C6, to the fiscal year starting month, that was entered in cell C4

• MONTH(C6)>=C4

Is that comparison TRUE or FALSE

IF Formula for TRUE or FALSE

That month comparison is inside an Excel IF formula, which uses the TRUE or FALSE result of that test, to add 1 or zero to the year number.

• IF(MONTH(C6)>=C4,1,0)

How the IF Function Works

IF is one of the Logical functions in Microsoft Excel, and there are 3 parts (arguments) to the IF function syntax:

1. logical_test: TEST something, such as the value in a cell.
2. value_if_true: Specify what should happen if the test result is TRUE.
3. value_if_false: Specify what should happen if the test result is FALSE.

IF Function Video

This short video shows the steps for setting up a simple IF formula.

Note: The written steps for the Fiscal Year continue below the video.

Excel IF Function for Fiscal Year

Here are those 3 arguments, used in our fiscal year IF formula:

• logical_test: MONTH(C6)>=C4
• value_if_true: 1 (add 1 to the year)
• value_if_false: 0 (add zero to the year)

And here is the full fiscal year formula that was entered in C8:

• =YEAR(C6) + IF(MONTH(C6)>=C4,1,0)

Fiscal Year Result Check

Using the date August 1, 2022 as an example, for fiscal year starting in July, here is the arithmetic for the calculation:

• Year: 2022
• Month: 8
• Start Month: 7
• 8 >= 7: TRUE
• 2022 + 1 = 2023

Video: CHOOSE Function for Fiscal Calculation

The formulas in the following sections use the CHOOSE function, to select the applicable fiscal month or fiscal quarter.

To see how the CHOOSE function works, watch this short video tutorial. It shows a few examples, starting with a formula for fiscal quarter.

Calculate Fiscal Month - CHOOSE Function

To calculate the fiscal month, you can use the CHOOSE Function. The CHOOSE function returns a value from a list, based on an index number.

Based on the month number, the fiscal month number can be returned from a list of numbers.

In this example, the fiscal year starts in July, so January is fiscal month 7.

For the months of the year, from January to December, the fiscal months are in this order: 7,8,9,10,11,12,1,2,3,4,5,6

Those month numbers can be typed into the CHOOSE formula, or you can refer to the cells that contain the numbers.

There are three different Excel formulas in the following sections, to show how this works.

Option 1 - List the Numbers

To calculate the Fiscal Month with the CHOOSE function, the date is entered in cell C6.

The following formula is entered in cell C11:

=CHOOSE(MONTH(C6),7,8,9,10,11,12,1,2,3,4,5,6)

If the date in cell C6 is March 22, 2015, the MONTH function will return 3 as the month number.

The formula returns the 3rd number from the list of fiscal months -- 9

7,8,9,10,11,12,1,2,3,4,5,6

Option 2 - Link to Cells

To calculate the Fiscal Month with the CHOOSE function, the date is entered in cell C6.

Fiscal months are entered in the reference table, in cells G6:R6

The following formula is entered in cell K11:

=CHOOSE(MONTH(C6),G6,H6,I6,J6,K6,L6,M6,N6,O6,P6,Q6,R6)

If the date in cell C6 is March 22, 2015, the MONTH function will return 3 as the month number.

The formula returns value from the 3rd reference in the list -- cell I6

G6,H6,I6,J6,K6,L6,M6,N6,O6,P6,Q6,R6

That cell contains the value 9

Calculate Fiscal Quarter - CHOOSE Function

To calculate the fiscal quarter, you can use the CHOOSE function. The CHOOSE function returns a value from a list, based on an index number.

Based on the month number, the fiscal quarter number can be returned from a list of numbers.

In the example shown below, the fiscal year starts in April, so January is fiscal quarter 4.

For the months from January to December, the fiscal quarters are in this order: 4,4,4,1,1,1,2,2,2,3,3,3

• Those numbers are listed across row 6, in the Fiscal Qtrs cells
• The month numbers are entered in row 7, starting with month 10.

Those quarter numbers can be typed into the CHOOSE formula, to calculate the fiscal quarter for a specific date.

Use the CHOOSE Function

To calculate the Fiscal quarter with the CHOOSE function, the date is entered in cell C6.

The following formula is entered in cell C11:

=CHOOSE(MONTH(C6),4,4,4,1,1,1,2,2,2,3,3,3)

If the date in cell C6 is March 22, 2015, the MONTH function will return 3 as the month number.

The formula returns the 3rd number from the list of fiscal months -- 4

4,4,4,1,1,1,2,2,2,3,3,3

Download the Sample File

Click here to download the Fiscal Year sample file. It is zipped, and in xlsx format, and does not contain any macros.

Related Tutorials

Date Functions

Fiscal Year - Pivot Table

Fiscal Year Weekdays - Pivot Table

Budget Forecast

CHOOSE Function 

Group by Fiscal Year