Skip to main content

How to calculate age (convert birthdate to age) quickly in Excel?

Author: Kelly Last Modified: 2025-04-07
A screenshot showing an Excel spreadsheet with birthdate data in the first column and age calculation results in the adjacent column

Imagine you're working with a list of birth dates in Excel and need to show their precise current ages. How can you efficiently achieve this? This article provides tips for easily calculating age from a date of birth in Excel.

Method A:
Calculate age from date of birth with formulas

Method B:
Easily calculate age from date of birth without remembering formulas


Calculate age from date of birth with formulas

In this section, we've outlined various formulas to calculate age from the date of birth under different scenarios:

(AD) Calculate age by Kutools with a few clicks

A screenshot of the Kutools for Excel interface showing the age calculation tool

Looking to effortlessly calculate ages up to today or any given date and present them in diverse formats such as years, months, weeks, days, or a year + month + day combination?

Kutools for Excel simplifies this task! No need for manual formula entries – Kutools streamlines the entire process, offering a user-friendly solution for all Excel users.

Discover more here: Easily calculate age from date of birth without remembering formulas.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now


Convert birthdate to age with INT function

We can calculate age by subtracting the birth date from the current date. Then, by using the INT function, we can display the age in complete years as follows:

Select a blank cell where you want the age to be displayed, input the below formula and press the Enter key. Then, select the result cell and drag its fill handle (the small green square in the lower-right corner of the cell) down to get all results.

=INT((TODAY()-A2)/365)

A screenshot showing the Excel formula bar with the formula for calculating age

Notes:

  • To get someone's age at a certain date, replace the "TODAY()" function in the formula with that specific date. For instance, if the particular date is in cell B2, use the formula below:
    =INT((B2-A2)/365)
    A screenshot of the formula in Excel showing the calculation of age using a specified date in cell B2
  • Using this formula in Excel, which calculates age by dividing the number of days between two dates by 365, usually yields fairly accurate results but isn't completely reliable. It can produce errors in cases involving leap years. An alternative approach is to divide by 365.25 to account for leap years, but this too can be inaccurate if no leap year occurs within the date range.

Convert birthdate to age with the YEARFRAC function

The YEARFRAC function is more accurate for calculating age from a date of birth because it considers the exact number of days in each year, including leap years. By setting the third argument to 1, YEARFRAC uses the actual day count in its calculation, ensuring precision, especially in cases involving leap years.

In an empty cell where you want the age calculated, enter the formula below and press Enter. To replicate this calculation for other data, drag the fill handle of the result cell downward.

=ROUNDDOWN(YEARFRAC(A2, TODAY(), 1), 0)

A screenshot showing the formula in the Excel formula bar to calculate accurate age


Convert birthdate to age with the DATEDIF function

The DATEDIF function in Excel can be used to calculate age from a date of birth. By using "y" in the unit argument of the function, it calculates the number of complete years between two dates.

Select an empty cell to display the age, input the formula provided below, and press the Enter key. Then, click on the cell with the result and drag its fill handle downwards to apply the formula to other cells and obtain all results.

=DATEDIF(A2,NOW(),"y")

A screenshot showing the formula in the Excel formula bar to calculate age in full years

Note: In the above DATEDIF formula, "y" calculates the complete years from the date in cell A2 to today. You can substitute "y" with "m", "d", "md", "ym", or "yd" to calculate different aspects of the date difference:

  • "M": The number of complete months in the given period.
  • "D": The number of days in the given period.
  • "MD": The difference between the days of the given two dates. The months and years of the dates are ignored.
  • "YM": The difference between the months of the given two dates. The days and years of the dates are ignored
  • "YD": The difference between the days of the given two dates. The years of the dates are ignored.

Display age in Year + Month + Day format with the DATEDIF function

To calculate age in the format of Year + Month + Day, combine three DATEDIF formulas in Excel, each with a different unit argument.

Select an empty cell to display the age, input the formula provided below, and press the Enter key. Then, click on the cell with the result and drag its fill handle downwards to apply the formula to other cells and obtain all results.

=DATEDIF(A2,TODAY(),"Y") & " Years, " & DATEDIF(A2,TODAY(),"YM") & " Months, " & DATEDIF(A2,TODAY(),"MD") & " Days"

A screenshot of Excel showing a formula for calculating age in Year + Month + Day format using the DATEDIF function


Easily calculate age from date of birth without remembering formulas

Kutools for Excel's "Date & Time Helper" feature simplifies age calculations, allowing you to easily convert birthdates to age with just a few clicks, without the need to remember formulas. It offers the flexibility to calculate age up to the current day or a specific date, and provides results in various formats like years, months, weeks, days, or a combination of years + months + days. This user-friendly tool streamlines the process, making it quick and accessible for all Excel users.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...
  1. Select a blank cell to display the age. Then click "Kutools" > "Formula Helper" > "Date & Time Helper".
    A screenshot of the Kutools for Excel menu with the Formula Helper > Date & Time Helper option selected
  2. In the "Date & Time Helper" dialog box, please configure as follows.
    1. Go to the "Age" tab;
    2. In the "Date of Birth" box, select the cell containing the birth date you wish to calculate the age from;
    3. Choose the "Today" option in the To section;
    4. Choose the desired age output format, like years, months, days, or a combination, from the "Output result type" drop-down list;
    5. Click the OK button.
      A screenshot of the Date & Time Helper dialog in Kutools showing the configuration to calculate age from date of birth

Result

Then the age is populated in the selected cell. Select the result cell and then drag its fill handle all the way down to get all ages.

A screenshot showing the calculated age displayed in a cell, with the result cell's fill handle being dragged down to populate the age in other rows

Notes:

  • To display the age in the Year + Month + Day format, select "Year + Month + Day" from the "Output result type" drop-down list. The result will appear as illustrated in the screenshot below. Additionally, this feature supports displaying age in months, weeks, or days, catering to your specific requirements.
    A screenshot showing age calculated in Year + Month + Day format using Kutools
  • Want to access the Date & Time Helper feature? Download Kutools for Excel now! Beyond this, Kutools boasts a myriad of 300+ other features. Don't wait, give it a try today!

Related Articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!