Calculate Years Until a Future Date in Excel

Excel Formula to Calculate Years Until a Certain Date

Formula Overview

To determine how many years remain until a specified future date from the current date, you can use the DATEDIF function in Excel. The formula is structured as follows:

=DATEDIF(TODAY(), target_date, "y")

Function Explanation

• TODAY(): This function returns the current date. It updates automatically each time the worksheet is recalculated.
• target_date: This should be a cell reference containing the specific date you want to calculate the years until or a date entered directly in quotes (e.g., "2025-12-31").
• "y": This argument specifies that you want the difference in complete years.

Practical Example

Assuming you want to find out how many years are left until December 31, 2025, you would place the target date in a cell, for example, A1.

1. Enter 12/31/2025 in cell A1.
2. Use the following formula in another cell (e.g., B1):

=DATEDIF(TODAY(), A1, "y")

Result Interpretation

• If today is October 1, 2023, the formula =DATEDIF(TODAY(), A1, "y") would return 2, indicating there are 2 complete years until the specified date.

Important Note

• The DATEDIF function is not readily visible in Excel's formula autocomplete; however, it is valid and functional. You may need to ensure that your target_date is valid and occurs after the current date.

Conclusion

The use of the DATEDIF function in conjunction with TODAY() provides an efficient way to calculate the number of complete years until a specified date, assisting in various planning and forecasting scenarios.