Contextures

Fiscal Year Calculations

Learn how to calculate the fiscal year and fiscal month for a specific date. The formulas are based on the Fiscal Year starting date or starting month.

Calculate the Fiscal Year

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

In this example, the starting month is entered in cell C4, and the date is entered in cell C6.

The following formula is entered in cell C8:

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

The formula result shows the fiscal year for the date.

NOTE: Format cell C8 as General Number format -- it might show a Date format if you recalculate it.

fiscal year calculation

How It Works

The formula calculates the year for the date entered in cell C6.

=YEAR(C6)

Then, the IF function compares the month number for the date entered in cell C6, to the fiscal year starting month in cell C4

IF(MONTH(C6)>=C4

If the month number is greater than or equal to that number, 1 is added to the year.
If the month number is less than that number, 0 is added to the year.

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

Calculate the 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 from January to December, the fiscal months are in this order: 7,8,9,10,11,12,1,2,3,4,5,6

fiscal month choose

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

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

fiscal month choose

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

fiscal month choose cell

Calculate the 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 this example, 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

fiscal quarter table

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

fiscal quarter choose

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.

Get All the Excel News

For regular Excel news, tips, videos, and special offers, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Related Tutorials

Fiscal Year - Pivot Table

Budget Forecast

CHOOSE Function  

Group by Fiscal Year  

SUM Function  

VLOOKUP Function  

INDEX and MATCH   

Count Functions  

INDIRECT Function  

Search Contextures Sites

 

pivot power premium

 

 

30 Excel Functions in 30 Days

 

Excel Data Entry Popup List

 

 

 

excel chart tools

 

 

Last updated: October 19, 2016 6:54 PM