Formula Errors

How to Calculate a Date Five Years After the Next Birthday in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs an Excel formula to calculate a date that is exactly five years after a person's next birthday, using an application approval date and their birth date.

Product: Excel
Device & OS: not provided
Scenario: Calculating administrative future dates based on multiple criteria (an approval date and a birth date) without manual calendar checks.
Observed behavior: A dynamic formula is required to output the correct future date or return a blank cell if the input data is missing.

Before you start

Ensure you have your application approval dates in column A (e.g., cell A2) and birth dates in column B (e.g., cell B2), and verify both columns are formatted as Dates rather than Text.

Solution 1Recommended

Use a Combination of LET, DATE, and EDATE Functions

This comprehensive formula calculates the exact required date by evaluating the approval year and adding five years to the next occurring birthday, while gracefully handling empty cells.

This solution utilizes the LET function to assign a variable to the intermediate date calculation, keeping the formula concise. It also uses EDATE to accurately measure 60 months (5 years) into the future, accounting for leap years automatically.

Select the target cell

Click on cell C2 (or wherever you want the result to appear) to make it the active cell.

Enter the formula

Type or paste the following formula into the formula bar: =IF(OR(A2="",B2=""),"",LET(d,DATE(YEAR(A2)+5,MONTH(B2),DAY(B2)),IF(d<EDATE(A2,60),EDATE(d,12),d)))

Apply and drag

Press Enter to calculate the result. You can then click and drag the fill handle at the bottom-right corner of C2 down to apply this formula to the rest of your data rows.

Formula Breakdown: The LET function defines 'd' as the birthday in the year that is 5 years after the approval year. The IF(d<EDATE(A2,60), EDATE(d,12), d) logic checks if that birthday falls before the exact 5-year anniversary. If it does, it adds 12 months to push it to the NEXT birthday.

Advanced Data Calculation

Easily Calculate Complex Dates with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like LET, EDATE, and DATE, making complex administrative calculations and future date projections a breeze.

1. Open your data in WPS Spreadsheet: Launch WPS Office, open your spreadsheet, and ensure your Approval Dates and Birth Dates are correctly formatted.
2. Paste the formula: Select your target cell and paste the provided LET and EDATE formula exactly as written.
3. Calculate and fill: Press Enter to instantly calculate the future date, and use the drag-and-drop fill handle for bulk processing.

Fully compatible with Microsoft Excel formulas, formatting, and functionsSupports dynamic variables and advanced logical functions like LET and IFLightweight, fast, and completely free for seamless daily office work

microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return a #NAME? error?

This error typically occurs if you are using an older version of Excel (prior to Excel 2021 or Microsoft 365) that does not support the LET function. To fix this, you would need to upgrade your software or rewrite the formula by replacing the 'd' variable with the full DATE function every time it appears.

How does the EDATE function work in this scenario?

The EDATE function adds or subtracts a specific number of months to a given date. In this formula, EDATE(A2, 60) adds exactly 60 months (which equals 5 years) to the application approval date, effectively finding the 5-year anniversary.

How can I modify this to calculate 10 years instead of 5?

To change the duration to 10 years, simply change the '+5' in YEAR(A2)+5 to '+10', and change the '60' in EDATE(A2,60) to '120' (since 10 years is 120 months).

Excel & Data Problems