Excel Formula for Fiscal Year: Step-by-Step Guide

Formulas & functions · Updated November 22, 2026

The simplest Excel formula for determining a fiscal year is =YEAR(A2)+(MONTH(A2)>=FiscalStartMonth), where A2 contains the date and FiscalStartMonth is the month number (1–12) that begins your fiscal year. For example, if your fiscal year starts in July (month 7), the formula =YEAR(A2)+(MONTH(A2)>=7) returns 2025 for any date from July 1, 2024, through June 30, 2025. This works because the logical test (MONTH(A2)>=7) evaluates to TRUE (1) for months July–December and FALSE (0) for January–June, adding one year to the calendar year when the date falls in the latter half of the calendar year.

Understanding Fiscal Years vs. Calendar Years

A fiscal year is any 12-month period used for accounting or budgeting that does not necessarily align with the calendar year (January–December). Many organizations choose a fiscal year that matches their business cycle—retailers often use February–January to capture holiday sales, while government agencies may use October–September. In Excel, the key is to convert a calendar date into the fiscal year label (e.g., “FY2025”) based on your defined start month. The formula above gives you the numeric year; you can format it as text with ="FY"&(YEAR(A2)+(MONTH(A2)>=FiscalStartMonth)) to display “FY2025”.

How the Formula Works

The formula leverages two Excel functions: YEAR() extracts the calendar year from a date, and MONTH() extracts the month number (1 for January, 12 for December). The comparison MONTH(A2)>=FiscalStartMonth returns TRUE (which Excel treats as 1) or FALSE (0). When you add that result to the calendar year, you effectively shift the year forward by one for dates that occur after the fiscal start month. For instance, with a July start, a date of June 30, 2025, has MONTH()=6, so 6>=7 is FALSE, giving YEAR=2025+0=2025. A date of July 1, 2025, has MONTH()=7, so 7>=7 is TRUE, giving 2025+1=2026. This correctly labels July 2025 as the start of FY2026.

Step-by-Step: Building the Formula

  • Assume your dates are in column A (A2:A100) and you have a cell (say C1) containing the fiscal start month as a number (e.g., 7 for July).
  • In cell B2, enter: =YEAR(A2)+(MONTH(A2)>=$C$1). Use absolute reference $C$1 so you can copy the formula down.
  • Press Enter, then double-click the fill handle (the small square at the bottom-right of the cell) to copy the formula down the column.
  • To display as “FY2025”, use: ="FY"&(YEAR(A2)+(MONTH(A2)>=$C$1)).
  • If you want to show the fiscal year as a date (e.g., the start of the fiscal year), use: =DATE(YEAR(A2)+(MONTH(A2)>=$C$1),$C$1,1).

For example, with dates in A2:A5 as 1/15/2024, 7/1/2024, 12/31/2024, and 6/30/2025, and C1=7, the formula returns 2024, 2025, 2025, and 2025 respectively. The text version returns “FY2024”, “FY2025”, “FY2025”, and “FY2025”.

Handling Fiscal Years That Start in January

If your fiscal year matches the calendar year (start month = 1), the formula still works: =YEAR(A2)+(MONTH(A2)>=1). Since MONTH(A2)>=1 is always TRUE, it adds 1 to every year, giving you the next calendar year—which is wrong. To avoid this, use a nested IF: =IF($C$1=1,YEAR(A2),YEAR(A2)+(MONTH(A2)>=$C$1)). Alternatively, use a more general formula: =YEAR(A2)-($C$1>1)*(MONTH(A2)<$C$1). This subtracts 1 for dates before the start month when the start month is not January. For a July start, this gives the same results as the simpler formula, but it also handles January correctly.

Common Problems and Solutions

  • Problem: Formula returns a decimal or text instead of a number. Ensure the cell format is General or Number, not Text. If you used the text version with &, it’s expected to be text.
  • Problem: Dates are stored as text. If your dates are not recognized as dates, MONTH() may return an error. Use DATEVALUE() to convert text, or use Data > Text to Columns to fix the format.
  • Problem: Fiscal year starts in a month but you need a fiscal quarter. Use =ROUNDUP((MONTH(A2)-FiscalStartMonth+1)/3,0) to get the quarter number, but be careful with negative values—use =MOD(MONTH(A2)-FiscalStartMonth,12)+1 to get the month within the fiscal year first.
  • Problem: You need to handle fiscal years that span two calendar years (e.g., July–June). The formula already handles this correctly because it adds 1 for dates after the start month.

Alternatives: Using IF, CHOOSE, or LET

While the simple formula is elegant, you might prefer a more explicit approach using IF: =IF(MONTH(A2)>=$C$1,YEAR(A2)+1,YEAR(A2)). This is easier to read for beginners. For more complex logic, CHOOSE can map month numbers to fiscal years, but it requires a list of 12 values. If you have Excel 365, you can use LET to define a variable: =LET(m,MONTH(A2),YEAR(A2)+(m>=$C$1)). This is more efficient if you use the month multiple times. For a dynamic array, use =YEAR(A2:A100)+(MONTH(A2:A100)>=$C$1) in Excel 365 to spill results automatically.

Formatting and Displaying Fiscal Year Labels

To make your fiscal year labels look professional, you can use custom number formatting. First, create a helper column with the numeric fiscal year using the formula above. Then select that column, press Ctrl+1 (or Cmd+1 on Mac) to open Format Cells, go to Custom, and enter "FY"0 in the Type box. This displays 2025 as “FY2025” without changing the underlying value. Alternatively, use the TEXT function: =TEXT(YEAR(A2)+(MONTH(A2)>=$C$1),""FY"0"). Note that TEXT returns text, so you lose the ability to do numeric calculations on the result.

Frequently Asked Questions

Q: What if my fiscal year starts on a specific day, like July 1, not just a month?
A: The formula assumes the fiscal year starts on the first day of the start month. If your fiscal year starts mid-month (rare), you need to compare the full date: =YEAR(A2)+(A2>=DATE(YEAR(A2),FiscalStartMonth,FiscalStartDay)). For example, if it starts on July 15, use =YEAR(A2)+(A2>=DATE(YEAR(A2),7,15)). This adds 1 only if the date is on or after that specific date.

Q: How do I calculate the fiscal year in Google Sheets?
A: The same formula works in Google Sheets: =YEAR(A2)+(MONTH(A2)>=7). The only difference is that in Google Sheets, you might use array formulas with ARRAYFORMULA if you want to apply it to a range without dragging, but the basic formula is identical.

Q: Can I use this formula to get the fiscal year as a date (e.g., the start of the fiscal year)?
A: Yes, use =DATE(YEAR(A2)+(MONTH(A2)>=$C$1),$C$1,1) to return the first day of the fiscal year. For example, for a date of 3/15/2025 with a July start, it returns 7/1/2025. Format the cell as a date to see it correctly.

Q: Why does my formula return a year like 1905 or a negative number?
A: This usually happens when the date cell contains a text string that Excel interprets as a year, or when the cell format is Text. Ensure the date is a real date (check by using ISDATE() in Google Sheets or ISNUMBER() in Excel). If the date is missing, MONTH() returns 0, and YEAR() returns 1900, so the formula gives 1900 or 1901. Use =IF(A2="","",YEAR(A2)+(MONTH(A2)>=$C$1)) to handle blanks.

Q: How do I calculate the fiscal year for multiple years at once?
A: In Excel 365, you can use a dynamic array formula: =YEAR(A2:A10)+(MONTH(A2:A10)>=$C$1). This spills results for all rows in A2:A10. In older Excel, you must copy the formula down. For Google Sheets, wrap it in ARRAYFORMULA: =ARRAYFORMULA(YEAR(A2:A10)+(MONTH(A2:A10)>=7)).

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.