Function Problems

How to Calculate a July-to-June Fiscal Year in Excel

Kushani Nimanthika Oct 7, 2026 868 views

Question details

The user needs a method to calculate and assign dates to a July-to-June fiscal year format within a spreadsheet.

How to Calculate a July-to-June Fiscal Year in Excel

Product: Excel
Device & OS: not provided
Scenario: Preparing financial reports or data models where the fiscal year runs from July to June (e.g., mapping July 2023 through June 2024 to FY2023) instead of a standard calendar year.
Observed behavior: The user requires a formula that will systematically evaluate standard date values and output the corresponding fiscal year for proper categorization.

Before you start

Ensure that the cells containing your dates are formatted as proper Date values in Excel, rather than plain text, so the date functions can process them accurately.

Solution 1Recommended

Use the EDATE and YEAR Functions

The most efficient way to calculate a July-to-June fiscal year is to use EDATE to shift the date back by six months, and then extract the year.

Because the fiscal year starts exactly six months into the calendar year (July), shifting any date back by six months aligns it with the correct fiscal calendar year. For example, March 2024 shifts back to September 2023, properly placing it in Fiscal Year 2023.

Select an empty cell

Click on the cell where you want the calculated fiscal year to be displayed.

Enter the formula

Type the formula =YEAR(EDATE(A2,-6)), assuming your original date is located in cell A2, and press Enter.

Add a text label (Optional)

If you want the result to display as a specific format like 'FY2023', modify your formula to: ="FY"&YEAR(EDATE(A2,-6)).

Apply to the entire column

Select the cell with the formula, click and hold the small square at the bottom-right corner (fill handle), and drag it down to apply the calculation to your remaining rows.

Custom Labels: By concatenating text using the ampersand (&) symbol, you can customize the fiscal year prefix to match your organization's exact reporting standards (e.g., 'FY-' or 'Fiscal ').

Solution 2

Create a Comprehensive Date Table for Advanced Reporting

For complex financial models using SUMIFS, Power Query, or Power Pivot, it is highly recommended to build a dedicated Date Table.

Efficient Financial Reporting

Calculate Fiscal Years Seamlessly with WPS Spreadsheet

WPS Office provides full support for advanced date functions like EDATE and YEAR. It offers a smooth experience for managing financial reporting, building date tables, and categorizing fiscal calendars.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open your financial document, and identify the column containing your calendar dates.
2. Apply the fiscal year formula: Click an adjacent empty cell and type the formula =YEAR(EDATE(A2,-6)) to compute the fiscal year.
3. Fill the data effortlessly: Double-click the fill handle in the bottom-right corner of the cell to instantly copy the fiscal year calculation down your entire dataset.

100% compatibility with Microsoft Excel formulas, including EDATE and YEAR.Lightweight and optimized for fast performance, ideal for large financial datasets.Free to download with a familiar, easy-to-use tabbed interface.Built-in support for PivotTables and advanced data aggregation.

microsoft office alternative - wps office

Frequently Asked Questions

How do I calculate a fiscal quarter for a July-to-June year?

You can calculate the fiscal quarter by shifting the date and using the ROUNDUP function. The formula =ROUNDUP(MONTH(EDATE(A2,-6))/3,0) will shift the date back 6 months, extract the month, and divide by 3 to return the correct fiscal quarter (1 through 4).

What if my fiscal year starts in October instead of July?

You simply need to adjust the number of months shifted in the EDATE function. Since October is 9 months into the calendar year, you shift backward by 9 months. The formula would be =YEAR(EDATE(A2,-9)).

Why is my EDATE formula returning a #VALUE! error?

The #VALUE! error usually means the referenced cell contains text instead of a valid date. Check your source cell (e.g., A2) and ensure it is formatted as a Short Date or Long Date, and doesn't contain hidden text characters or spaces.

Can I use the calculated fiscal year column in a PivotTable?

Yes. Once you have populated a column with the fiscal year formula, ensure it has a clear header (like 'Fiscal Year'). You can then insert a PivotTable and drag this new field into the Rows, Columns, or Filters area to summarize your data accordingly.

Excel & Data Problems