How to Get the Next Year from a Date Using an Excel Formula
Question details
The user needs an Excel formula to automatically display the year immediately following a specified date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating a future year based on a given date entry in a spreadsheet column.
- Observed behavior
- The user wants to extract the year from a date in one column and add one to it, resulting in the subsequent year appearing in the adjacent cell.
Ensure your dates in the source column are formatted as actual Date values in Excel, rather than plain text, so the YEAR function can recognize them correctly.
Use the YEAR and IF Functions Together
The most reliable way to get the following year is to extract the year from the date and add 1, using an IF function to correctly handle blank cells in your data.
If you apply a basic addition formula to an empty cell, Excel typically returns the year 1901. Combining the YEAR function with an IF statement ensures that empty cells remain blank.
Click on the cell where you want the next year to appear, such as cell B1 located next to your original date.
Type the formula =IF(A1="","",YEAR(A1)+1) into the formula bar and press Enter.
Select the cell with the new formula, click the small square at the bottom-right corner, and drag the fill handle down to apply the formula to the rest of the rows.
Use the Basic YEAR Function
If your spreadsheet has no blank cells and you just want the simplest method available, you can use the YEAR function by itself.
Calculate Dates Easily with WPS Spreadsheet
WPS Spreadsheet provides seamless support for all standard date and time functions, including YEAR, MONTH, and DAY. You can quickly process datasets, perform date calculations, and format your results with an intuitive interface.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the original date values.
- 2. Input the formula: Select the adjacent cell where you want the next year to display, enter =IF(A1="","",YEAR(A1)+1), and press Enter.
- 3. Fill the formula down: Drag the fill handle at the bottom right of the active cell to seamlessly copy the calculation down the entire column.
Frequently Asked Questions
Why does my YEAR formula return a #VALUE! error?
This error typically occurs if the referenced cell contains text instead of a valid date format. Ensure your source data is recognized as a Date by Excel. You can fix this by selecting the cell, right-clicking, choosing 'Format Cells', and setting it to 'Date'.
How can I return the date exactly one year later instead of just the 4-digit year?
To get the exact same day and month but one year in the future, use the EDATE function. Enter the formula =EDATE(A1, 12) into the target cell, and ensure the cell is formatted as a Date.
Why does the formula =YEAR(A1)+1 return 1901 when the source cell is empty?
In spreadsheet software, an empty cell evaluates to zero, which the system interprets as the start of its calendar: January 0, 1900. Adding 1 to the year 1900 results in 1901. Use the IF function to verify if a cell is blank before calculating, such as =IF(A1="","",YEAR(A1)+1), to avoid this default result.
