Function Problems

How to Convert Date Ranges into Academic Years in Excel

WPS Editor Sep 30, 2026 871 views

Question details

The user needs to automatically convert standard calendar dates into academic year labels (e.g., 2023-2024) in an adjacent column.

How to Convert Date Ranges into Academic Years in Excel

Product: Excel
Device & OS: not provided
Scenario: Organizing academic or school data where standard calendar dates need to be dynamically categorized into specific academic years.
Observed behavior: Dates in one column correctly output the corresponding academic year label in a separate column based on a standard July-to-June academic calendar.

Before you start

Ensure your source column is formatted as standard dates and identify which month your academic year officially begins (commonly July or September).

Solution 1Recommended

Use the LET Function to Calculate the Academic Year

This is the most efficient method to evaluate the month and dynamically adjust the year for dates falling in the second half of the academic year.

The LET function allows you to assign a calculation to a variable (in this case, 'Y') to simplify the formula. This specific formula assumes the academic year switches in July, meaning January through June belong to the academic year that started the previous calendar year.

Select the target cell

Click on the empty cell where you want the academic year label to appear.

Enter the formula

Type the formula =LET(Y,YEAR(C43)-(MONTH(C43)<=6),Y&"-"&Y+1), replacing 'C43' with the cell reference of your actual date.

Apply to the entire column

Press Enter to generate the academic year. Then, click and drag the fill handle (the small square at the bottom-right corner of the cell) down to apply the formula to the remaining dates.

Use the LET Function to Calculate the Academic Year

Formula Breakdown: The formula subtracts 1 from the calendar year if the month is January through June (represented by <=6). It then concatenates the starting year, a hyphen, and the following year.

Solution 2

Use the IF Function for Older Excel Versions

If you are using a version of Excel that does not support the LET function, you can achieve the same result using a combination of IF, YEAR, and MONTH.

Powerful Spreadsheet Tool

Easily Manage Academic Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like LET and IF, allowing you to seamlessly calculate academic years and organize school data without compatibility issues.

1. Open your file: Launch WPS Spreadsheet and open the document containing your academic dates.
2. Select the target cell: Click on the empty cell where you want the academic year to be displayed.
3. Input the formula: Type the formula =LET(Y,YEAR(A2)-(MONTH(A2)<=6),Y&"-"&Y+1) into the formula bar.
4. Apply to all rows: Press Enter, then double-click the small square at the bottom-right of the cell to fill the formula down the column.

Fully compatible with Microsoft Excel formulas and date formats.Supports advanced data analysis functions to easily sort academic years.Lightweight, fast, and completely free to use for everyday tasks.Cross-platform support for Windows, Mac, iOS, and Android.

microsoft office alternative - wps office

Frequently Asked Questions

How do I change the start month of the academic year in the formula?

You can easily adjust the month condition in the formula. For example, if your academic year starts in September instead of July, change the condition from MONTH(C43)<=6 to MONTH(C43)<=8.

Why is my formula returning a #NAME? error?

The #NAME? error typically occurs if you are using an older version of the software that does not support the LET function. If this happens, use the alternative IF function method provided in the second solution.

Can I output the academic year in a short format like 23-24?

Yes, you can modify the formula using the RIGHT function to extract only the last two digits of the year. The formula would look like this: =LET(Y,YEAR(C43)-(MONTH(C43)<=6),RIGHT(Y,2)&"-"&RIGHT(Y+1,2)).

Excel & Data Problems