FAQ: How Do I Calculate Fiscal Year?

https://commons.wikimedia.org/wiki/Category:Aurora_electronic_calculator_DT210Establishing a fiscal year is imperative to the disposal of certain records i.e. financial records. The fiscal year may vary between government offices and state agencies.  In building this tool, I used the months of August and September as planning factors in order to align the tools with the fiscal year end of state agencies and local government offices. To access the tool, you can click the following : Fiscal Year Calculator for Local Government Offices and Fiscal Year Calculator for State Agencies.

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 C3, and the date is entered in cell C1.

The following formula is entered in cell C10:

=YEAR(C1) + IF(MONTH(C1)>=C3,1,0)

The formula result shows the fiscal year for the date.

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

How It Works

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

=YEAR(C1)

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

IF(MONTH(C1)>=C3

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(C1)>=C3,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 September, so January is fiscal month 9. For the months from January to December, the fiscal months are in this order: 9,10,11,12,1,2,3,4,5,6,7,8.

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(C1),9,10,11,12,1,2,3,4,5,6,7,8)

If the date in cell C1 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 — 11

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

Option 1

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(C1),G1,H1,I1,J1,K1,L1,M1,N1,O1,P1,Q1,R1)

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 I1

G1,H1,I1,J1,K1,L1,M1,N1,O1,P1,Q1,R1

The value in the cell is 11.

Option 2

Refer to the fiscal month cells for Jan-Dec, in the CHOOSE formula

In this case, the date is in fiscal month 11.

 Calculate the Fiscal Quarter

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 September, 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 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 C1.

The following formula is entered in cell C6:

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

If the date in cell C1 is August 2, 2017, the MONTH function will return 2 as the month number.

The formula returns the 2nd number from the list of fiscal months — 2

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

 


This tool represents a way to calculate the fiscal year, month, and quarter.  There are more techniques on these calculations at the following links:

Comment below to share your experiences— successful and challenging— with using this fiscal year calculation tool or building your own calculation tool.

One thought on “FAQ: How Do I Calculate Fiscal Year?

  1. Benjamin: Thank you for an interesting post. I have never had occasion to calculate fiscal month or quarter, but I do routinely calculate by fiscal year. I can then filter by fiscal year and group all closed case files having a common fiscal year. When disposition time comes they all have the same year. I have been using a very simple formula in MS Access (FY Close: IIf(Month([closed date])>=10,Year([closed date])+1,Year([closed date]))) to return the fiscal year. The other one is a bit more involved as it needs to pull out a fiscal year out of the middle of a case number but it is only in two digits. FY Open: CInt(IIf(Mid([case id],7,2)>17,Mid([case id],7,2)+1900,Mid([case id],7,2)+2000)). So a case from 2000 will only have 00 in the middle. Cases that are older still will be from the 1990s which would produce confusing Sort results. The DateAdd() function is also pretty handy to calculating disposition. These formulas are pretty simple but they are adequate for my needs. Thanks to TSLAC for its outstanding blog site.

Comments are closed.