Have you ever needed to calculate someone's age in Excel? Perhaps for a personal project, HR task, or financial analysis? Fear not! Excel offers multiple ways to perform this calculation seamlessly. We'll explore two methods to determine a person's age using Excel's powerful functions.
Method 1: Using DATEDIF()
One of the simplest ways to calculate age in Excel is by leveraging the DATEDIF() function. This function isn't documented in older Excel versions like 5, 7, or 97, but it's readily available in Excel 2000 and later versions.
Let's take an example: Suppose a person was born on January 1st, 1960.
- Years lived: 64 = DATEDIF(BirthDate, TODAY(), "y")
- Months lived: 2 = DATEDIF(BirthDate, TODAY(), "ym")
- Days lived: 17 = DATEDIF(BirthDate, TODAY(), "md")
Putting it all together, we get a text version of the age: "Age is 64 Years, 2 Months, and 17 Days"
Here's the formula for easy reference: ="Age is "&DATEDIF(BirthDate, TODAY(), "y")&" Years, "&DATEDIF(BirthDate, TODAY(), "ym")&" Months, and "&DATEDIF(BirthDate, TODAY(), "md")&" Days"
Method 2: Another Approach
Alternatively, you can calculate age using a formula that may yield decimal places representing months. This method provides a more precise age, including fractional parts for months.
For instance, if someone is 20.5 years old, the ".5" represents 6 months.
Here's how you can implement it: Age = (TODAY() - BirthDate) / 365.25
Let's apply this to our example:
- Birth Date: January 1st, 1960
- Age: 64.21 = (TODAY() - BirthDate) / 365.25
EXAMPLE:
Download PDF
0 Comments