- Excel Tutorial
- Excel Introduction
- Excel Addition(+)
- Excel Subtraction(-)
- Multiplication(*)
- Excel Division(/)
- Excel IF Function
- Excel SUMIF
- Count & CountIF
- Excel IFerror
- Excel Pivot Table
- Move Pivot Tables
- Excel Drop-Down list
- Conditional Formatting
- Excel Hyperlink
- Excel Vlookup
- Excel Hlookup
- Excel Protect Sheet
- Excel Macros
- Excel Insert Images
- Excel Concatenate
- Excel Text To Column
- Remove Duplicates
- Excel Cell Formatting
- Group in Excel
- Excel Comments
- Excel Freeze Panes
- Excel Charts
- Print in Excel
- Excel Transpose
- Reminders in Excel
- Reduce Excel File Size
- Percentage in Excel
- Substitute Function
- RANK Function
- ROUND Function
- Cell References
- Date Function
- Text Functions
- Percentage Calculation
- Text to Columns
- Rounddown in Excel

EXCEL FORMULAS Excel Tutorial Excel Introduction Excel Addition(+) Excel Subtraction(-) Multiplication(*) Excel Division(/) Excel IF Function Excel SUMIF Count & CountIF Excel IFerror Excel Pivot Table Move Pivot Tables Excel Drop-Down list Conditional Formatting Excel Hyperlink Excel Vlookup Excel Hlookup Excel Protect Sheet Excel Macros Excel Insert Images Excel Concatenate Excel Text To Column Remove Duplicates Excel Cell Formatting Group in Excel Excel Comments Excel Freeze Panes Excel Charts Print In Excel Excel Transpose Reminders in Excel Reduce Excel File Size Percentage in Excel Substitute Function RANK Function ROUND Function Cell References Date Function Text Functions Percentage Calculation Text to Columns Rounddown in Excel

Age Calculation by DOB | YEARFRAC() function | DATEDIF()

Calculating an individual’s age on Excel is helpful in several situations. Mainly, the HR department uses the **age calculation formula in Excel** to calculate an employee's retirement date and find their pension based on the number of years spent working for your company.

Age calculation is also necessary for lots of business and other operations. Whether you want to calculate the age of an individual or experiment with Excel formulas, this tutorial will teach you various ways to determine someone's age in Excel.

You can calculate the age of an individual by their birthday.

The best and easiest way to calculate someone's age is to subtract the date of birth from the current date. That is how we manually determine a person's age, isn't it? The same formula can be used in Excel.

Let's say the birthdate is in cell C3. The **age calculation in Excel formula** is as follows:

**=(TODAY()-C3)/365**

Using the formula **TODAY()-C3**, you get the difference between the current date and your date of birth in days, then divide that number by **365** for years.

However, this formula results in decimal numbers as shown below.

The **INT** function can be used to round it down to the nearest integer.

**=INT((TODAY()-C3)/365)**

The problem with this age formula is that it is based on the assumption that every year is exactly 365 days.

As every fourth year (leap year) has 366 days, you divide the date by 365.25, but even this is not guaranteed to be accurate.

Hence this formula works well in a few cases but is flawed when the person is born during a leap year.

The **YEARFRAC** function in Excel provides an accurate **age calculation formula in Excel.** With the YEARFRAC function, you can calculate age from date of birth that returns the fraction of the year for two dates.

Here is the Excel formula for the YEARFRAC function:

**YEARFRAC(DOB, TODAY(), Basis)**

Here,

Select **Basis = 1,** which tells Excel to divide the number of days per month by the number of days per year.

As shown in the previous example, DOB is in cell C, hence the formula to be used is

**=YEARFRAC(C3, TODAY(), 1)**

The decimal number can be rounded down using the INT function,

**=INT(YEARFRAC(C3, TODAY(), 1))**

DATEDIF() function is one of the most popular **age calculation formula in Excel.** Let us now see how to use this function to calculate age in years, months, and days.

The syntax of the DATEDIF function is

**=DATEDIF(start_date, end_date, unit)**

Here,

Start_date - This can be the date of birth.

End_date - This can be the current date, TODAY().

Unit - The DATEDIF function can produce six different sets of results, depending on what unit you use. Here is a complete list of the units you can use:

- Y – indicates the number of completed years in the specified period.
- M – indicates the number of completed months in the specified period.
- D – indicates the number of completed days in the specified period.
- MD – counts how many days are in the period, but does not include the ones in the Years and Months that have already passed.
- YM – indicates the number of months in the period, but does not include the ones in the Years and Months that have already passed.
- YD – indicates the number of days in the period, but doesn’t count the ones in the Years that have already passed.

As shown previously, DOB is in Cell C3, DATEDIF formula to calculate age by year is as shown:

**=DATEDIF(C3, TODAY(), "y")**

Similar to the year formula, the DATEDIF function can be used to calculate age by year, months, and days using the following formula:

**=DATEDIF(C3,TODAY(),"Y") & DATEDIF(C3,TODAY(),"YM") & DATEDIF(C3,TODAY(),"MD")**

where

- 1. =DATEDIF(C3, TODAY(), "Y") - Gives the number of years
- 2. =DATEDIF(C3, TODAY(), "YM") - Gives the number of months
- 3. =DATEDIF(C3,TODAY(),"MD") - Gives the number of days

Since the result shown in the above image does not make any sense, let us add commas and text to help differentiate year, month, and dates using the IF function.

=IF(DATEDIF(C3, TODAY(),"y")=0,"",DATEDIF(C3, TODAY(),"y")&" years, ")& IF(DATEDIF(C3, TODAY(),"ym")=0,"",DATEDIF(C3, TODAY(),"ym")&" months, ")& IF(DATEDIF(C3, TODAY(),"md")=0,"",DATEDIF(C3, TODAY(),"md")&" days")

That's it! We have covered several ways to calculate age in Excel. We hope this tutorial was helpful to you. If you have any questions or feedback, please let us know in the comments box below.

- How to do Addition in Excel?
- Transpose in Excel
- How to do Percentage in Excel?
- How to do Rank in Excel?
- How to do Freeze in Excel?

Subscribe for more videos on our Youtube channel. if you like our tutorial please Like and Subscribe to our Facebook and Youtube.

To Learn:

Tutorials in a simple Way.

Excel with downloadable samples.

HTML with sample website code.

PowerPoint presentations.

MS Word for offical use.