To find a financial year from a date in Excel, enter the date in A2 and use =YEAR(A2)+(MONTH(A2)>=4) for a financial year that starts in April and is named for the year it ends in. Change 4 to the month your financial year begins. Before you start, confirm both the start month and whether your organization names each year by its starting year or ending year; those conventions can produce different labels for the same date.
What You’ll Need
- A column of dates stored as real Excel dates, not text that only looks like a date.
- The month your financial year starts. For example, an April start is month 4, and a July start is month 7.
- Your organization’s naming convention: the year the financial year starts, or the year it ends. If the convention matters for reporting, verify it against your organization’s guidance.
- A blank column for the result. This guide assumes the first date is in A2 and the financial-year result will go in B2.
Step-by-Step
- Check how your financial year is defined. A financial year, also called a fiscal year, is a 12-month reporting period that may begin in a month other than January. For an April-to-March financial year, 1 April 2024 through 31 March 2025 is one complete period. Many organizations call that FY2025 because it ends in 2025; others call it FY2024 because it starts in 2024. Excel cannot know which label your organization expects, so settle this before building a formula.
- Put your dates in one column. Enter or paste the dates into column A, starting in A2, and put a heading such as Date in A1. Keep one date per row. If you have an existing table, use its date column instead and update the cell reference in the formula. A date displayed as 04/05/2025 may mean April 5 or May 4 depending on regional settings, so confirm ambiguous dates before calculating.
- Enter the ending-year formula for an April start. With the date to classify in A2 and the financial year starting in April and named by its ending year, enter this formula in B2:
=YEAR(A2)+(MONTH(A2)>=4). YEAR returns the calendar year, and MONTH returns a number from 1 through 12. The comparison is TRUE for April through December and FALSE for January through March; in this addition, Excel treats TRUE as 1 and FALSE as 0. For a date of 15 May 2024, the result is 2025. For 15 February 2025, it is also 2025. - Check the boundary dates. Test one date just before the start month, one on the first day of the start month, and one near the period end. With an April start, 31 March 2024 returns 2024, 1 April 2024 returns 2025, and 31 March 2025 returns 2025. This confirms the formula moves to the next ending-year label exactly when the new financial year begins. If your dates include times, the formula still evaluates the month, so the time does not change the result.
- Fill the formula down the column. Select B2 and copy or fill the formula into the rows alongside your dates. In Excel for Microsoft 365 on Windows, you can use the fill handle at the lower-right corner of B2 and double-click it to fill down alongside a continuous date list. You can also copy B2, select the destination cells, and paste. Excel for Mac and Excel for the web use the same formula and have a fill handle; the ribbon layout may differ slightly. Check the first and last filled rows to ensure the formula references each corresponding date.
- Use a different start month when needed. In the April formula, replace 4 with the number of your starting month: 1 for January, 7 for July, or 10 for October. For example, with a date in A2 and a financial year that starts in July and is named for its ending year, use
=YEAR(A2)+(MONTH(A2)>=7). A date in June 2024 returns 2024, while a date in July 2024 returns 2025. The start month must be an integer from 1 to 12. - Use a cell for the start month if you want to change it easily. Enter the start month number in B1, such as 4 for April, and put the date in A2. With that layout, enter this formula in C2:
=YEAR(A2)+(MONTH(A2)>=$B$1). The dollar signs keep the reference to B1 fixed when you fill the formula down. This is useful when a workbook may be reused for different reporting calendars: change B1 rather than editing every formula. Add a clear heading such as Start month above B1 so another reader knows what the number represents. - Choose a starting-year label if that is your convention. For an April-to-March period labeled by the year it starts, use the date in A2 and enter
=YEAR(A2)-(MONTH(A2)<4)in B2. A date in May 2024 returns 2024, and a date in February 2025 also returns 2024. To make this version adjustable, put the start month in B1 and use=YEAR(A2)-(MONTH(A2)<$B$1)in C2. The formula subtracts one for dates before the start month, which assigns January-to-March dates to the financial year that began the previous calendar year. - Display a label if you prefer text. A numeric result such as 2025 is usually easier to sort, filter, or use in calculations. If you need a label such as FY2025, use the date in A2 and an April start with ending-year naming, and enter
="FY"&(YEAR(A2)+(MONTH(A2)>=4))in B2. For 15 May 2024, the displayed result is FY2025. You can change the text FY to match your reporting style, but keep the year calculation consistent with your chosen convention.
Common Mistakes
Using the wrong year-label convention: The formula may calculate the correct period boundary but still show a year that looks wrong if you expected the starting year and it returns the ending year, or vice versa. For an April start, the two common formulas deliberately label the same period differently. Confirm which convention your reports, accounting system, or employer uses.
Using a calendar-year formula for a non-January start: =YEAR(A2) returns the calendar year only. It does not group April-to-March dates into the same financial year. Use the month comparison formula when the financial year does not start in January.
Entering a start month name instead of a month number: If a formula compares MONTH(A2) with B1, B1 must contain a number such as 4, not the text April. A text month can cause incorrect results or a formula error. If you want a readable input, place the number in one cell and add a nearby note saying April.
Misreading dates imported from another region: Excel may interpret a pasted date according to your computer’s regional date settings. A value intended as 4 May could be read as 5 April. Verify the source format and check sample dates around month boundaries; changing the cell’s display format alone does not repair a date that was imported incorrectly.
Confusing display formatting with the formula result: A custom number format may make a value appear as a year or label without changing the underlying value. If you use a text formula, the result is text; if you use a year formula, the result is numeric. Keep numeric years when you plan to sort or calculate with them.
Tips
Keep the starting month in a clearly labeled input cell if the workbook will be used again next year or for several entities. Lock that cell reference with dollar signs in the formula, as in $B$1, so it does not move when copied down. If different departments have different financial-year start months, store each department’s month separately and make sure each row’s formula refers to the right one.
Format the date column consistently so it is easy to review. Select the dates and use Home > Number Format to choose a date format that makes the day, month, and year unambiguous, such as 15-Apr-2024. The formatting changes how a valid date appears, not the date value used by YEAR and MONTH. Excel for Mac and Excel for the web have date-format controls as well, though their ribbon presentation can vary.
Use a few known dates as a small validation set before relying on a long list. Include dates on the last day before the financial year starts and the first day of the new year. If the data is part of a formal report, compare a few calculated labels against a trusted source. Keep the result numeric if you need to sort financial years in order; a text label such as FY2025 is convenient to read but may sort alphabetically in some situations.
If blank rows are common, you can prevent them from displaying a misleading year. With dates in A2 and an April start using ending-year labels, enter this formula in B2: =IF(A2="","",YEAR(A2)+(MONTH(A2)>=4)). It returns a blank when A2 is blank, otherwise it calculates the year. Fill it down as usual. This check is useful when a formula is copied into rows that are not yet populated.
Troubleshooting
The formula returns a number that does not match the expected year. First check the start month and year-label convention. Then test the date against the boundary: for an April start, dates from 1 April through 31 December move to the next ending-year label, while dates from 1 January through 31 March keep their calendar year. If your financial year starts in another month, make sure the formula uses that month number.
Excel shows a formula error or treats the formula as text. Confirm that the cell begins with an equals sign and that the cell is not formatted as Text. If it is Text, change the cell format to General, then re-enter the formula. In some regional settings, Excel expects semicolons rather than commas between function arguments; these examples have no function-argument commas, so that setting should not affect them. Check that parentheses are balanced and the referenced date cell is correct.
The formula returns a strange value or a date error. Check that A2 contains a genuine date value. Select it and inspect the formula bar, or temporarily change its number format to a date. If it is text, convert it using Data > Text to Columns and select the appropriate date order when prompted, or re-import the source with the correct regional date setting. Do not assume that applying a date format to text will convert it.
Some rows show the same year or the wrong result after filling down. Inspect a filled formula to ensure its date reference changes from A2 to A3, A4, and so on. In the adjustable formula, B1 should remain fixed as $B$1. Also confirm there are no blank or incorrectly imported dates mixed into the list. If the first result is correct but later ones are not, the fill operation or cell references are often the cause.
The returned year is right, but the report expects FY2025. The calculation may be numeric by design. Use the text-label formula if you need the FY prefix, or format the report separately. Make sure you do not add a prefix that implies a different convention from the numeric year you calculated.
Frequently Asked Questions
How do I calculate a financial year that starts in April? With the date in A2 and the year named for the year it ends in, use =YEAR(A2)+(MONTH(A2)>=4). It assigns 1 April 2024 to 2025 and 31 March 2025 to 2025. Use the starting-year version if your organization names that same period FY2024.
Can I find the financial year if it starts in July? Yes. Replace the 4 in the April formula with 7. For example, a date in June 2024 belongs to the period ending in 2024, and a date in July 2024 belongs to the period ending in 2025 under ending-year naming.
How can I return the financial year as FY2025 instead of 2025? Use ="FY"&(YEAR(A2)+(MONTH(A2)>=4)) for an April start and ending-year labels. Replace FY with your preferred prefix. The result is text, so retain a separate numeric-year column if you need numeric sorting or calculations.
Does this work in Excel for Mac or Excel for the web? Yes. YEAR, MONTH, the comparison, and the formulas in this guide work in Microsoft 365 for Windows, Mac, and the web. Ribbon controls and fill behavior can look slightly different, but the calculation does not change.
Why does my date show the wrong financial year after I paste it? The pasted value may have been interpreted using a different date order, or it may be text rather than a real date. Check the underlying date and the source’s day-month-year order. Correct the imported date value first; changing its display format alone cannot fix an incorrectly interpreted date.
Related
- How to Enable Wrap Text in Excel and Adjust Row Height
- Excel Trust Center: How to Open and Configure Settings
- How to Enable Undo in Excel and Restore the Undo Button
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Change to Light View in Outlook: Steps for Every VersionTo change Outlook to a light view, open its appearance or theme settings and choose Light, or turn…
- How to Apply a Hanging Indent in Word for ReferencesTo apply a hanging indent in Word, select the paragraph or paragraphs, open the Paragraph settings, choose Hanging…
- Excel Max Rows per Sheet: Limits and WorkaroundsIn Microsoft Excel for Microsoft 365, a worksheet can contain a maximum of 1,048,576 rows and 16,384 columns.…
- How to Create File Labels in Word: A Step-by-Step GuideTo create file labels in Microsoft Word, open Mailings > Labels, choose the label brand and product number…
- Combine Cells in an Excel Table: Merge or Join DataIn Microsoft Excel, you cannot merge cells inside a formatted Excel Table. To make a merged cell, first…
- Google Sheets Data Validation from a Range ExplainedTo create Google Sheets data validation from a range, select the cells that need a dropdown, choose Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.