You can use the following formulas to convert date of birth to age in Excel:
Formula 1: Convert Date of Birth to Age in Years (e.g. 23 years)
=DATEDIF(A2,NOW(),"y")
Formula 2: Convert Date of Birth to Age in Decimal Years (e.g. 23.567 years)
=YEARFRAC(A2,NOW())
Formula 3: Convert Date of Birth to Age in Years, Months, Days (e.g. 23 years, 6 months, 25 days)
=DATEDIF(A2,NOW(),"y")&" years, "&DATEDIF(A2,NOW(),"ym")&" months, "&DATEDIF(A2,NOW(),"md")&" days"
Each formula assumes that the date of birth is located in cell A2.
The following examples show how to use each formula in practice with the following list of birth dates in Excel:
Example 1: Convert Date of Birth to Age in Years
To convert the date of birth to age for cell A2, we can type the following formula into cell B2:
=DATEDIF(A2,NOW(),"y")
We can then click and drag this formula down to each remaining cell in column B:
Column B displays the age for each birth date in column A in terms of years.
Example 2: Convert Date of Birth to Age in Decimal Years
To convert the date of birth to an age in decimal years for cell A2, we can type the following formula into cell B2:
=YEARFRAC(A2,NOW())
We can then click and drag this formula down to each remaining cell in column B:
Column B displays the age for each birth date in column A in terms of decimal years.
Example 3: Convert Date of Birth to Age in Years, Months, Days
To convert the date of birth to an age in years, months and days for cell A2, we can type the following formula into cell B2:
=DATEDIF(A2,NOW(),"y")&" years, "&DATEDIF(A2,NOW(),"ym")&" months, "&DATEDIF(A2,NOW(),"md")&" days"
We can then click and drag this formula down to each remaining cell in column B:
Column B displays the age for each birth date in column A in terms of years, months, and days.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate the Number of Months Between Dates in Excel
How to Convert Date to Month and Year Format in Excel
How to Calculate Average by Month in Excel
I am trying to make my real birth of january 17th, 2007 date be changed into a new birth date of january 1st, 2024.
Hi Reuben…To convert a date of birth (DOB) into age and then change the DOB to a new date in Excel, you can follow these steps:
### Converting Date of Birth to Age
1. **Enter the Date of Birth**:
– Enter the date of birth in a cell (e.g., `A1`).
2. **Calculate the Age**:
– Use the `DATEDIF` function to calculate the age. This function calculates the difference between two dates.
Formula:
“`excel
=DATEDIF(A1, TODAY(), “Y”)
“`
– `A1`: The cell containing the date of birth.
– `TODAY()`: The current date.
– `”Y”`: The unit to return (years).
### Example:
1. **Enter Date of Birth** in cell `A1`:
– `1/17/2007`
2. **Calculate Age** in cell `B1`:
“`excel
=DATEDIF(A1, TODAY(), “Y”)
“`
### Changing the Date of Birth to a New Date
To change the date of birth from `1/17/2007` to `1/1/2024`, you simply need to replace the old date with the new date in the cell.
### Example:
1. **Enter New Date of Birth** in cell `C1`:
– `1/1/2024`
### Combining Both Tasks
If you want to calculate age based on the new date and compare it with the age calculated from the original date, you can follow these steps:
1. **Original Date of Birth** in cell `A1`:
– `1/17/2007`
2. **New Date of Birth** in cell `C1`:
– `1/1/2024`
3. **Calculate Age from Original Date of Birth** in cell `B1`:
“`excel
=DATEDIF(A1, TODAY(), “Y”)
“`
4. **Calculate Age from New Date of Birth** in cell `D1`:
“`excel
=DATEDIF(C1, TODAY(), “Y”)
“`
### Full Example in Excel:
| A | B | C | D |
|————-|———-|————-|———-|
| Date of Birth | Age (Original DOB) | New Date of Birth | Age (New DOB) |
| 1/17/2007 | `=DATEDIF(A1, TODAY(), “Y”)` | 1/1/2024 | `=DATEDIF(C1, TODAY(), “Y”)` |
By entering these formulas in the respective cells, Excel will calculate the age based on both the original and new date of birth, allowing you to see the difference.
### Note
If you need to change a large number of dates of birth systematically, consider using a formula to adjust the dates. For example, to add 17 years to the original date:
“`excel
=DATE(YEAR(A1) + 17, MONTH(A1), DAY(A1))
“`
This formula adds 17 years to the date in cell `A1`, effectively changing `1/17/2007` to `1/1/2024` if adjusted appropriately. You can place this formula in the cell where you want the new date to appear.