How To Calculate Fiscal Year Based on Date in Excel/Sheets + Free Template
Oftentimes a fiscal year does not start on January first and companies will want data summarized by the fiscal year to be able to graph this data and compare year over prior year. This can be done utilizing the YEAR and MONTH functions in either Microsoft Excel or Google Sheets.
Steps to Calculate Fiscal Year from Date
- Format your date that you are trying to extract the fiscal year to mm-dd-yyyy
- Create a column that specifies what quarter the fiscal year starts
- Example: If your fiscal year starts in July add 7 to that column
- Utilize the YEAR and MONTH functions to extract the fiscal year as follows:
- Point cell A2 (or wherever it is in your spreadsheet) to the formatted date that you are trying to extract the year from
- Point cell B2 to the column where you have added what month your fiscal year starts (in the example above, if it was July then point it to the column where you have indicated it starts with 7)
Free Template – Copy to Extract Fiscal Year
Download your free copy here that you can easily copy and paste into an existing sheet or workbook to graph your own data by fiscal year.
What is in the template for fiscal year
- This template provides examples and formulas for the fiscal year starting in July.
- No need for you to recreate the formula, you will just need to copy and paste!
How to use the template for fiscal year
If you have a different fiscal year start month, simply change the column to whatever month number yours starts and the formula in the template will update.
Change the start of the month number for your specific fiscal year
- Example if your fiscal year starts on January 1st
- 1 = January, 2 = February, 3 = March, 4 = April, 5 = May, 6 = June, 7 = July, 8 = August, 9 = September, 10 = October, 11 = November, 12 = December
What date range is in the template for fiscal year
- The dates start from 1/1/2020 through 12/31/2028.
- Once you make a copy of the template you will be able to modify it for your own needs.