Excel is a powerful tool that can perform various calculations and functions. One of the common tasks that you may need to do in Excel is to calculate the age of a person based on their date of birth. This can be useful for various purposes, such as analyzing data, creating reports, or making decisions.
In this article, we will show you how to calculate age in Excel using different methods and formulas. We will also explain the basic theory behind each method and provide a detailed example with real data. By the end of this article, you will be able to calculate age in Excel with ease and accuracy.
The Basic
Before we dive into the formulas and methods, let’s understand the basic theory of calculating age in Excel. The age of a person is the difference between their date of birth and the current date or a specific date. Therefore, to calculate age in Excel, we need two dates: the date of birth and the reference date.
The date of birth is usually entered in a cell as a date value, such as 01/01/1990. The reference date can be either the current date or a specific date. The current date can be obtained by using the TODAY function, which returns the current date as a date value. A specific date can be entered manually in a cell or using the DATE function, which creates a date value from a given year, month, and day.
The difference between two dates can be calculated in various units, such as years, months, or days. Excel provides several functions and techniques to calculate the difference between two dates in different units. The most commonly used function is the DATEDIF function, which calculates the difference between two dates in a specified unit. The syntax of the DATEDIF function is as follows:
=DATEDIF(start_date, end_date, unit)
- start_date: The starting date or the earlier date in the calculation.
- end_date: The ending date or the later date in the calculation.
- unit: The unit of measurement to calculate the difference. It can be one of the following options:
- “y” – Calculates the difference in complete years.
- “m” – Calculates the difference in complete months.
- “d” – Calculates the difference in days.
- “ym” – Calculates the difference in months, excluding years.
- “yd” – Calculates the difference in days, excluding years.
- “md” – Calculates the difference in days, excluding years and months.
For example, the formula =DATEDIF(“01/01/1990”, “01/01/2020”, “y”) returns 30, which is the difference in years between the two dates. The formula =DATEDIF(“01/01/1990”, “01/01/2020”, “m”) returns 360, which is the difference in months between the two dates.
The DATEDIF function is not the only way to calculate the difference between two dates in Excel. There are other functions and techniques that can be used to achieve the same result. We will discuss some of them later in this article.
How to Calculate Age in Excel in Years
One of the simplest ways to calculate age in Excel is to use the DATEDIF function with the unit “y”. This will return the age in complete years, ignoring the months and days. For example, if the date of birth is in cell A2 and the reference date is in cell B2, the formula to calculate the age in years is:
=DATEDIF(A2, B2, “y”)
This formula will return the number of complete years between the date of birth and the reference date. For example, if the date of birth is 01/01/1990 and the reference date is 01/01/2020, the formula will return 30. However, if the reference date is 12/31/2019, the formula will return 29, even though the person is only one day away from turning 30.
To make the formula more dynamic, you can use the TODAY function instead of a specific date as the reference date. This will return the current date as a date value and update automatically every time you open the workbook. For example, the formula to calculate the age in years based on the current date is:
=DATEDIF(A2, TODAY(), “y”)
This formula will return the age in years as of today’s date. For example, if the date of birth is 01/01/1990 and today’s date is 02/01/2024, the formula will return 34.
How to Calculate Age in Excel in Months
Another way to calculate age in Excel is to use the DATEDIF function with the unit “m”. This will return the age in complete months, ignoring the days. For example, if the date of birth is in cell A2 and the reference date is in cell B2, the formula to calculate the age in months is:
=DATEDIF(A2, B2, “m”)
This formula will return the number of complete months between the date of birth and the reference date. For example, if the date of birth is 01/01/1990 and the reference date is 01/01/2020, the formula will return 360. However, if the reference date is 12/31/2019, the formula will return 359, even though the person is only one day away from completing another month.
To make the formula more dynamic, you can use the TODAY function instead of a specific date as the reference date. This will return the current date as a date value and update automatically every time you open the workbook. For example, the formula to calculate the age in months based on the current date is:
=DATEDIF(A2, TODAY(), “m”)
This formula will return the age in months as of today’s date. For example, if the date of birth is 01/01/1990 and today’s date is 02/01/2024, the formula will return 409.
How to Calculate Age in Excel in Days
Another way to calculate age in Excel is to use the DATEDIF function with the unit “d”. This will return the age in days, ignoring the years and months. For example, if the date of birth is in cell A2 and the reference date is in cell B2, the formula to calculate the age in days is:
=DATEDIF(A2, B2, “d”)
This formula will return the number of days between the date of birth and the reference date. For example, if the date of birth is 01/01/1990 and the reference date is 01/01/2020, the formula will return 10957. However, if the reference date is 12/31/2019, the formula will return 10956, even though the person is only one day away from completing another year.
To make the formula more dynamic, you can use the TODAY function instead of a specific date as the reference date. This will return the current date as a date value and update automatically every time you open the workbook. For example, the formula to calculate the age in days based on the current date is:
=DATEDIF(A2, TODAY(), “d”)
This formula will return the age in days as of today’s date. For example, if the date of birth is 01/01/1990 and today’s date is 02/01/2024, the formula will return 12419.
How to Calculate Age in Excel in Years, Months, and Days
Sometimes, you may want to calculate the age in Excel in more detail, such as in years, months, and days. For example, you may want to know that a person is 30 years, 5 months, and 15 days old, instead of just 30 years or 365 months. To do this, you can use a combination of the DATEDIF function and the & operator.
The & operator is used to join or concatenate two or more text strings or values in Excel. For example, the formula =“Hello” & “World” returns “HelloWorld”. You can use the & operator to join the results of the DATEDIF function with different units and some text identifiers, such as “years”, “months”, and “days”. For example, the formula to calculate the age in years, months, and days is:
=DATEDIF(A2, B2, “y”) & ” years, ” & DATEDIF(A2, B2, “ym”) & ” months, ” & DATEDIF(A2, B2, “md”) & ” days”
This formula will return the age in years, months, and days as a text string. For example, if the date of birth is 01/01/1990 and the reference date is 01/01/2020, the formula will return “30 years, 0 months, 0 days”. However, if the reference date is 12/31/2019, the formula will return “29 years, 11 months, 30 days”.
To make the formula more dynamic, you can use the TODAY function instead of a specific date as the reference date. This will return the current date as a date value and update automatically every time you open the workbook. For example, the formula to calculate the age in years, months, and days based on the current date is:
=DATEDIF(A2, TODAY(), “y”) & ” years, ” & DATEDIF(A2, TODAY(), “ym”) & ” months, ” & DATEDIF(A2, TODAY(), “md”) & ” days”
This formula will return the age in years, months,
and days as of today’s date. For example, if the date of birth is 01/01/1990 and today’s date is 02/01/2024, the formula will return “34 years, 1 month, 0 days”.
Example of Calculating Age in Excel
To illustrate how to calculate age in Excel using the formulas and methods we discussed above, let’s look at a detailed example with real data. Suppose we have a table of employees with their names and dates of birth, as shown below:
Table
| Name | Date of Birth |
|---|---|
| Alice | 01/01/1990 |
| Bob | 02/15/1985 |
| Charlie | 03/31/1992 |
| David | 04/20/1989 |
| Eve | 05/10/1991 |
We want to calculate the age of each employee in years, months, and days as of today’s date, which is 02/01/2024. To do this, we can use the following steps:
- Enter the current date in a cell, such as B1, using the TODAY function. The formula in B1 is =TODAY().
- Enter the formula to calculate the age in years, months, and days in the adjacent column, such as C2, using the DATEDIF function and the & operator. The formula in C2 is =DATEDIF(B2, $B$1, “y”) & ” years, ” & DATEDIF(B2, $B$1, “ym”) & ” months, ” & DATEDIF(B2, $B$1, “md”) & ” days”. Note that we use absolute references for the current date ($B$1) to prevent it from changing when we copy the formula down.
- Copy the formula in C2 down to the rest of the cells in column C. The table will look like this:
Table
| Name | Date of Birth | Age |
|---|---|---|
| Alice | 01/01/1990 | 34 years, 1 month, 0 days |
| Bob | 02/15/1985 | 38 years, 11 months, 17 days |
| Charlie | 03/31/1992 | 31 years, 10 months, 1 day |
| David | 04/20/1989 | 34 years, 9 months, 12 days |
| Eve | 05/10/1991 | 32 years, 8 months, 22 days |
This way, we can calculate the age of each employee in years, months, and days as of today’s date using Excel.
Other Approaches to Calculate Age in Excel
The DATEDIF function is not the only way to calculate age in Excel. There are other approaches that can be used to achieve the same result. Here are some of them:
- Using the YEARFRAC function: The YEARFRAC function returns the fraction of a year between two dates. For example, the formula =YEARFRAC(“01/01/1990”, “01/01/2020”) returns 30, which is the fraction of a year between the two dates. To convert the fraction of a year to years, months, and days, we can use the INT function and the MOD function. The INT function returns the integer part of a number, and the MOD function returns the remainder after dividing a number by another number. For example, the formula to calculate the age in years, months, and days using the YEARFRAC function is:
=INT(YEARFRAC(A2, B2)) & ” years, ” & INT(MOD(YEARFRAC(A2, B2), 1) * 12) & ” months, ” & INT(MOD(MOD(YEARFRAC(A2, B2), 1) * 12, 1) * 30) & ” days”
This formula will return the age in years, months, and days as a text string. However, this formula may not be very accurate, as it assumes that each month has 30 days and each year has 365 days. This may not be true for leap years or months with different numbers of days.
- Using the YEAR, MONTH, and DAY functions: The YEAR function returns the year of a given date, the MONTH function returns the month of a given date, and the DAY function returns the day of a given date. For example, the formula =YEAR(“01/01/1990”) returns 1990, the formula =MONTH(“01/01/1990”) returns 1, and the formula =DAY(“01/01/1990”) returns 1. To calculate the age in years, months, and days using these functions, we can use the following logic:
- To calculate the age in years, we subtract the year of the date of birth from the year of the reference date.
- To calculate the age in months, we subtract the month of the date of birth from the month of the reference date. If the result is negative, we add 12 to it and subtract 1 from the age in years.
- To calculate the age in days, we subtract the day of the date of birth from the day of the reference date. If the result is negative, we add the number of days in the previous month to it and subtract 1 from the age in months.
For example, the formula to calculate the age in years, months, and days using the YEAR, MONTH, and DAY functions is:
=IF(MONTH(B2) > MONTH(A2), YEAR(B2) – YEAR(A2), IF(MONTH(B2) < MONTH(A2), YEAR(B2) – YEAR(A2) – 1, IF(DAY(B2) >= DAY(A2), YEAR(B2) – YEAR(A2), YEAR(B2) – YEAR(A2) – 1))) & ” years, ” & IF(MONTH(B2) > MONTH(A2), MONTH(B2) – MONTH(A2), IF(MONTH(B2) < MONTH(A2), MONTH(B2) – MONTH(A2) + 12, IF(DAY(B2) >= DAY(A2), MONTH(B2) – MONTH(A2), MONTH(B2) – MONTH(A2) – 1))) & ” months, ” & IF(DAY(B2) >= DAY(A2), DAY(B2) – DAY(A2), DAY(B2) – DAY(A2) + DAY(EOMONTH(A2, -1))) & ” days”
This formula will return the age in years, months, and days as a text string. However, this formula is very long and complex, and it may be difficult to understand and maintain.
