In Excel, the EDATE function can be used to calculate a retirement date starting from a specific date such as a birth date. The EDATE function saves the stress of tedious counting using a calendar to calculate a retirement date. This article will also explain how to calculate the years remaining until the retirement date.
Excel EDATE function uses the following arguments:
- Start_Date: The start_date represents the cell range of the date to start counting from.
- Months: It dictates the number of months to count from the start_date.
- Years: This determines the number of times Excel is expected to repeat the count of months. It dictates the end date.
The EDATE function returns a date from the past or future in Excel. A positive value for a month(s) executes a date from the future while a negative value will execute a date from the past.
The EDATE function is entered into an Excel worksheet along with specified range, the number of months and years to be calculated.
Let us now try this Excel trick using an imagined data of ABC, Inc. employees. Below is the step-by-step to use the employee’s date of birth to calculate retirement date. It is assumed all workers are to retire at the age of 64.
- Enter details (name and date of birth) in Excel worksheet.
Figure 1. ABC, Inc. employees data as an example for EDATE function
- Use EDATE function to calculate retirement time.
Figure 2. EDATE function showing retirement date which is conspicuous
- Change the calculated retirement date to an understandable date format by changing the General selection in Home to either short date or long date format.
Figure 3. Excel worksheet showing the change from General option to Long Date
Figure 4. Calculated retirement date in understandable date format
- Use the DATEDIF function to calculate the remaining years before retirement.
Figure 5. Excel worksheet showing remaining years using DATEIF function
- Depending on the choice, the date displayed can be manipulated using either the short date or long date option.