In this example, the goal is to calculate a retirement date at age 60, based on a given birthdate. The simplest way to do this is with the EDATE function. The EDATE function will return a date n months in the future or pastwhen given a date and the number of months to traverse. In this case, we want a date 60 years from thebirthdate in column C, so the formula in D5 is:
=EDATE(C5,12*60) // 60 years from birthdateThe date comes from column C. For months, we need the equivalent of 60 years in months. Since most people don't know how many months are in 60 years, a nice way to do this is to embed the calculation in the formula like this:
12*60 // 720 months = 60 yearsInside the EDATE function, Excel will perform the math and return 720 directly to EDATE as the months argument. Embedding calculations this way can help make the assumptions and purpose of a formula easier to understand. To use a retirement age of 65, just adjust the calculation:
12*65 // 780 months = 65 yearsIn cell D5, returns the date March 10, 2023. As the formula is copied down column D, a different date is returned for each person in the list based ontheir birthdate.
Note: EDATE returns a date as aserial number, so apply a date number formatto display as a date.
End of month
To calculate a retirement date that lands at the end of the month, you can use the EOMONTH function instead of the EDATE function like this:
=EOMONTH(C5,12*60) // +60 years at end of monthEOMONTH works like EDATE, but always returns the end of the month. If there is a rule that people with birthdays that fall on the first of the month retire on the last day of the previous month, the formula can be adjusted like this:
=EOMONTH(C5,(12*60)-(DAY(C5)=1))Here, the logical expressionDAY(C5)=1 is subtracted from12*60 = 720. The DAY function returns the day of the birthdate. If the day is 1, the expression returns TRUE. Otherwise, the expression returns FALSE. The math operation of subtraction coerces TRUE to 1 and FALSE to zero. The result is that EOMONTH moves forward 719 months if a birthday falls on the first of the month, and 720 months if not. This moves first-of-month birthdays to the last day of the previous month.
Year only
To return the retirement year only, we can nest EDATE inside theYEAR functionlike this:
=YEAR(EDATE(C5,12*60)) // return year onlySince we already have the date in column D, the formula in column Eis:
=YEAR(D5) // year from date in D5The YEAR function returns the yearof the date returned by EDATE.
Years remaining
In column F, we calculate the years remaining with the YEARFRAC function like this:
=YEARFRAC(TODAY(),D5)This formula returns the difference between today's date and the calculated retirement date in column D. As the retirement date approaches, the years remaining will automatically decrease.To guard against a retirement date that has already passed, the formula in column F uses the SIGN function to change the years remaining to a negative number like this:
=YEARFRAC(TODAY(),D5)*SIGN(D5-TODAY()) // make negative if pastThe SIGN function simply returns the sign of a number as 1, -1, or 0. To use it, we subtract today's date from the retirement date. If the result is positive, the retirement date is in the future and SIGN returns 1, which does not affect the result from YEARFRAC.If the result is negative, the retirement date is in the past and SIGN returns -1, flipping the YEARFRAC calculation to a negative number. You can see the result in row 8, where the retirement date has already passed.
Other uses
This same approach can be used to calculate dates for a wide variety of use cases:
- Warranty expiration dates
- Membership expiration dates
- Promotional period end date
- Shelf life expiration
- Inspection dates
- Certification expiration
