How to calculate age from date of birth

This post explains that how to get age from the date of birth in a Cell. How to calculate birthdate to age using formulas in Excel. How to convert the birth of date to age quickly in Excel.

Calculate age from date of birth

If you have a table that contains birthdate data in a range of Cells in Excel, and you want to get the age from those people’s birthdate value, you can use a combination of the INT function, the TODAY function to create an excel formula .

You can use the TODAY function to get the current date and then is subtracted to the value of birthdate and then the number is divided by 365 to get the exact age value, last, using the INT function to convert the age value to the integer.

Assuming that you want to calculate date of birth to age in Cell B2, you can use the following formula:

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

Let’s see how this formula works:

=TODAY()

current date1

This formula returns the current date.

=(TODAY()-B2)/365

current date2

The current date subtracts the date of birth to get the number of days of birth.  The returned number is divided by 365 (one year) to get the exact numbers of years.  Then it goes into the INT function as its argument.

 

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

current date3

This formula will round a decimal down to an integer. So you can get the age from the date of birth.

Calculate age from date of birth with DATEDIF function

You can also create a formula based on the DATEIF function and TODAY function or NOW function. The DATEDIF function can return the number of complete year between the current date and birth date. So you can write down the following formula to convert date of birth to age quickly:

=DATEDIF(B2,TODAY(),"Y")

OR

=DATEDIF(B2,NOW(),"Y")

 get age from date of birth1

Calculate age from date of birth with YEARFRAC function

You can also create an excel formula based on the INT function or ROUNDDOWN function, the YEARFRAC function and the TODAY function to calculate age from birth of date.

The YEARFRAC function can be used to calculate the fraction of the year represented by the number of whole days between the date of birth and the current date.  Then you need to use the INT function or ROUNDDOWN function to round down the number returned by the YEARFRAC function to the integer value.

So you can write down the following formula:

=INT(YEARFRAC(B2, TODAY(), 1))

OR

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

get age from date of birth2


Related Formulas

  • Convert Date to Day of Week in Excel
    If you want to get the day name of the week as an abbreviation from a date, such as: Mon, Tue, etc. Enter the following TEXT formula in the Cell C1 that you want to get the day name of the week…
  • Convet Text Date dd/mm/yy to mm/dd/yyyy as Date Format
    If you want to convert dates in the text format to the date format as mm/dd/yyyy, you can use a combination of the DATE function, the VALUE function, the RIGHT function, the MID function, and the LEFT function to create a new excel formula….
  • Convert date to month name with Text Function
    If you want to convert the date to a month name, you can use the TEXT function with a specified format code like “mmm”in excel.  You can try to run the following TEXT formula:=TEXT(B1,”mmm”)…
  • Convert Date to text with Text Function in Excel
    you can use TEXT function in excel to convert dates to text in a specific format code. For example, The TEXT function can use the following patterns, like as: “mm/dd/yyyy”, “yyyy/mm/dd”, etc…
  • Convert date to month and year only in excel
    If you want to convert the date to month and year only, you can use “yyyymm” format code within the TEXT function in excel, so you can write down the below TEXT formula:=TEXT(date,”yyyymm”)…,
  • Convert date to month and day only in excel
    If you want to convert the date (mm/dd/yyyy) to month and day only, you can use “mm dd” format code within the TEXT function in excel, so you can write down the below TEXT formula: =TEXT(date,”mm dd”)…

Related Functions

  • Excel DATEDIF function
    The Excel DATEDIF function returns the number of days, months, or years between tow dates.The syntax of the DATEDIF function is as below:=DATEDIF (start_date,end_date,unit)…
  • Excel INT function
    The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.The syntax of the INT function is as below:= INT (number)…
Related Posts

Calculate Years Between Dates In Ms Excel

If you are an avid Ms Excel user, then you might have come across a task in which you needed to calculate the years between the dates; you might take it easy and do this task manually, which is also ...

How to Calculate Retirement Date and Remaining Years refer to Birth Date in Excel
Calculate Retirement Date 10

Every company has its own police about the retirement date for employees. Suppose employees will be retired from a certain age of 60, how can we calculate the retirement date for everyone base on their birthdays? Actually, there are several ...

How to Sum If Date is Greater or Less Than in Excel
Sum If Date is Greater or Less Than 1

If we sum for certain conditions, we'll use the SUMIF function in excel. For example, count the total sales for a period before of after a specified date. This article will help you to do sum for a specified period ...

How to Highlight Cell or Row If Date Is In Current Day/Week/Month in Excel
highlight cell or row if date8

This post will guide you how to highlight cell if date is the current day or is in the current week or month in Excel. How do I highlight row if date is in current week or month with conditional ...

How to Add the Current Month or Year in a Cell in Excel
add current date in cell6

This post will guide you how to add the current month or year into a cell or header or footer in your worksheet. How do I insert the current date or time in a cell with a formula in Excel. ...

Conditional Format Dates earlier than or Greater Than Today
format dates greater than or older than today5

This post will guide you how to use conditional formatting to identify dates earlier than or greater than today in Excel. How do I highlight dates greater than or less than today or a certain date with conditional formatting in ...

Insert Countdown Timer
insert countdown timer5

This post will guide you how to create a countdown timer in an Excel Worksheet. How do I insert a countdown timer in Excel. How to create a countdown timer clock in Excel. Create Countdown Timer Assuming that you want ...

Highlight the Dates if its over a year
highlight dates if its over year5

This post will guide you how to highlight the dates that are over 1 year in excel. How do I highlight the dates over a year old using conditional formatting feature in excel. How to use conditional formatting to highlight ...

Highlight Rows If Dates Have Passed
higlight rows if date passed5

This post will guide you how highlight rows if the dates in cells have passed from now in your worksheet. How do I highlight rows if dates have passed with the Conditional Formatting feature in excel. Highlight Rows If Dates ...

Transpose Multiple Columns into One Column
transpose multiple columns6

This post will guide you how to transpose multiple columns into a single columns with multiple rows in Excel. How do I put data from multiple columns into one column with Excel formula; How to transpose columns into single column ...

Sidebar