Excel: Convert Dates to Fiscal Years, Quarters, or Months
Excel can convert a date into a calendar month, fiscal year, or fiscal quarter with formulas, even when your fiscal year starts in a month other than January. Enter a valid Excel date and the fiscal year’s starting month; formulas can return a readable label or a number you can use for sorting and analysis.
Choose a date and fiscal-year convention
Excel stores dates as serial numbers, so formulas can compare and calculate dates rather than treating them as text. Before choosing a formula, decide what “fiscal year” means in your organization. A fiscal year that starts in July might be named for the year it begins (“FY2025”) or the year it ends (“FY2026”). Both conventions are used, so confirm which one your reports expect.
Assume the date is in A2 and the fiscal year begins in month number 7 (July). You can replace 7 with any month from 1 through 12. The formulas below label a fiscal year by its ending year, unless stated otherwise.
Convert a date to a fiscal year
This formula returns the fiscal year’s ending year as a number:
=YEAR(A2)+(MONTH(A2)>=7)
In Excel, comparisons such as MONTH(A2)>=7 evaluate to TRUE or FALSE. In arithmetic, TRUE acts as 1 and FALSE as 0. A date in July through December therefore adds one to its calendar year; a date from January through June does not.
To display a label such as FY2026, concatenate text with the result:
="FY"&(YEAR(A2)+(MONTH(A2)>=7))
If the organization names the fiscal year for its starting year instead, use this formula:
=YEAR(A2)-(MONTH(A2)<7)
For a July start, June 30, 2025 belongs to FY2025 when labeled by ending year, while July 1, 2025 belongs to FY2026. With starting-year labels, those dates are FY2024 and FY2025, respectively. The dates have not changed—only the naming convention has.
Calculate the fiscal quarter
For a July-start fiscal year, this formula returns a quarter number from 1 to 4:
=INT(MOD(MONTH(A2)-7,12)/3)+1
MOD wraps month offsets into a 12-month cycle, so July maps to the first month of the fiscal year, August to the second, and so on. INT groups those months into three-month quarters. To show a label such as Q1, use:
="Q"&(INT(MOD(MONTH(A2)-7,12)/3)+1)
For a different start month, replace 7 with that month number. For example, an October start uses 10. October through December then form Q1, January through March Q2, April through June Q3, and July through September Q4.
To include the fiscal year in a single label, combine the formulas:
="FY"&(YEAR(A2)+(MONTH(A2)>=7))&" Q"&(INT(MOD(MONTH(A2)-7,12)/3)+1)
This produces labels such as FY2026 Q1 for a date in July, August, or September 2025 under the ending-year convention.
Return a month or month label
For the calendar month number, use:
=MONTH(A2)
To return the full month name, use TEXT:
=TEXT(A2,"mmmm")
Use “mmm” for an abbreviated name such as Jul. TEXT returns text, which is convenient for display but may not sort chronologically as expected when sorted alphabetically. For reliable month sorting or grouping, keep a real date or numeric month value in the data and use the text label only for presentation.
A date can also be converted to the first day of its month, which remains a real date:
=DATE(YEAR(A2),MONTH(A2),1)
Format that result as “mmmm” or “mmm yyyy” using Format Cells. A true date is often better than a text month because it can be sorted, filtered, grouped, and used in further date calculations.
Use formulas with a configurable start month
Instead of typing the fiscal start month into every formula, place it in B1. Enter 7 for July, for example, and refer to B1. The fiscal year ending-year formula becomes:
=YEAR(A2)+(MONTH(A2)>=$B$1)
The fiscal quarter formula becomes:
=INT(MOD(MONTH(A2)-$B$1,12)/3)+1
The dollar signs keep the reference fixed when you copy the formula down a column. Check that B1 contains a whole number from 1 to 12. A cell with the month name “July” is not the same input as the number 7 for these formulas.
Enter the formula in the first row of a new column, then fill it down using Excel’s fill handle or copy and paste. When you add rows to an Excel table, calculated-column formulas can automatically extend to new records.
Common errors and useful alternatives
- The date is stored as text. If A2 looks like a date but YEAR or MONTH returns an error or an unexpected value, convert it to a real Excel date. Depending on its format, use Date formatting, Text to Columns, or DATEVALUE; check the result because date interpretation can depend on regional settings.
- The year label seems off by one. Verify whether the fiscal year is named for its beginning or ending year. This is a convention, not an Excel calculation error.
- Quarter and fiscal year use different rules. Apply the same start-month setting to both. Otherwise, a row can show a quarter that does not belong to its displayed fiscal year.
- Sorting labels gives an odd order. Labels such as “Q1” and “Q2” are text. Keep numeric year and quarter fields, or a real date, for sorting and use labels for display.
- The fiscal year does not follow a simple month boundary. The formulas here assume a fiscal year starts on the first day of a month and has four quarters of three calendar months each. A 4-4-5 calendar, a year ending on a particular weekday, or another custom calendar needs a calendar mapping table or more specialized logic.
For reports, a useful alternative is to add separate columns for fiscal year, fiscal quarter, and month-start date. PivotTables can then summarize those fields, while a date table can keep organization-specific labels consistent across multiple worksheets and reports.
Frequently Asked Questions
How do I convert a date to a fiscal year in Excel?
For a fiscal year starting in July and labeled by its ending year, use =YEAR(A2)+(MONTH(A2)>=7). Change 7 to the fiscal start month and adjust the formula if your organization labels years by their starting year.
How do I get a fiscal quarter from a date?
Use =INT(MOD(MONTH(A2)-7,12)/3)+1 for a July start. Replace 7 with the fiscal start month. The result is a number from 1 to 4.
Can Excel calculate fiscal years automatically?
Yes. A formula can classify each date using the fiscal start month. For changing policies or nonstandard calendars, keep the start month in a cell or use a lookup table so the rules are easier to maintain.
Why does Excel show the wrong month or fiscal year?
Check that the source value is a real date rather than text, and confirm the fiscal start month and year-naming convention. Regional date formats can also cause an entered date to be interpreted differently than intended.
How do I display a month as text without losing date sorting?
Keep the underlying value as a real date, such as the first day of the month, and apply a custom date format like mmmm or mmm yyyy. This displays a readable label while retaining date-based sorting.