Calculate Age at the Date of an Event in Excel
Question details
The user needs a formula to calculate someone's age at the time a specific event occurred. If the age cell is blank for that row, the formula should pull the most recent age from above, and leave the cell blank if no event date is provided.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking ages corresponding to event dates in a spreadsheet where some age inputs are missing and need to be inherited from the previous entry.
- Observed behavior
- Generating a dynamically calculated age based on the current age, the current year, and the event year, while gracefully handling blank cells.
Ensure your data is organized properly in columns, typically with Column A containing the current age and Column B containing the event date formatted as a valid date in Excel.
Use an IF and LOOKUP Combination Formula
This method uses a nested formula to verify if the event date exists and intelligently fills in missing age data from previous rows before calculating the age at the time of the event.
By combining the IF, LOOKUP, YEAR, and TODAY functions, Excel can logically determine the age someone was during a past event, even if their current age isn't listed on every single row.
Click on the cell where you want the calculated age to appear, for example, cell C1.
Type or paste the following formula into the formula bar: =IF(B1<>"",IF(A1<>"",A1,LOOKUP(2,1/(A$1:A1<>""),A$1:A1))-(YEAR(TODAY())-YEAR(B1)),"")
Press Enter to execute the formula. The age at the time of the event will be displayed.
Click and drag the fill handle (the small square at the bottom-right corner of the cell) downwards to apply this calculation to the rest of the rows in your dataset.
Calculate Complex Dates and Ages Seamlessly in WPS Office
WPS Spreadsheet is fully equipped to handle advanced data tracking, including nested IF and LOOKUP formulas for calculating ages based on dynamic event dates. It offers complete compatibility with Excel formulas, ensuring a seamless experience.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your spreadsheet containing the age and event date data.
- 2. Select the target cell: Click on the destination cell where the event age should be displayed.
- 3. Apply the formula: Paste the provided IF and LOOKUP formula into the formula bar and press Enter.
- 4. Drag to fill: Use the fill handle to quickly drag and apply the formula to the remaining rows in your column.
Frequently Asked Questions
Can I use DATEDIF instead to calculate exact age at the time of an event?
Yes. If you have the person's exact birth date in one cell and the exact event date in another, it is much simpler to use the formula =DATEDIF(BirthDate_Cell, EventDate_Cell, "Y") to calculate the exact age in years at the time of the event.
Why does the formula return an error or unexpected number?
This usually happens if the event date column contains text instead of actual dates, or if there is no nonblank age anywhere in the rows above the current cell for the LOOKUP function to reference.
How do I modify the formula if my data is in different columns?
You can adjust the cell references in the formula to match your sheet. For example, if your current age data is in Column D and the event date is in Column E, replace all instances of A with D, and all instances of B with E in the formula.
