How to calculate age from date of birth in Excel

 

In the article we will learn how to calculate the age in Excel and what is the formula we have in Excel to calculate the age.

 

Excel considers dates as numbers. Each date is assigned a unique serial number. For example, the 18th February 2014 was its serial number as 41684. Fortunately, you don’t need to know this but the fact that all dates have numerical values can be very useful. Windows uses the 1900 date system in which 2nd January 1900 is date serial 2 and so on.

When you type a date into a cell. Excel shows you a date but is thinking of a number.

To find out the serial number of a date, follow the below mentioned steps

  • Select the cell containing the date.
  • Press the key “CTRL+1”, the dialog box of “Format Cells” will appear.
  • In “Number” tab select “General” from the category list, the serial number will appear in the sample box and click on OK.

You can make use of these numbers in all sorts of ways. You can add a number to a date to give a date that number of days later (or subtract a number to get a date before), you can subtract one date from another to find out how many days are in between. There are lots of ready-made date functions too.

To calculate age from date of birth we will use  “Today” function along with the“INT” function.

Let’s take an example and understand how excel calculate age from date of birth.

I have employee data in which Column A contains“Employee Name”, Column B contains “Date of Birth” and in Column C we need to calculate the age as of today.

 

img3

 

To calculate age from date of birth, follow the below mentioned steps:

    • Select the Cell C2 and write the formula for age calculation
    • =INT((TODAY()-B2)/365)
    • Press enter on the keyboard
    • The function will return the age of an employee

 

jatt

 

  • If you want age along with the text (Years), you have to write this formula =INT ((TODAY ()-B2)/365) &” Years”.
  • Copy the formula by pressing the key “CTRL+C” and paste in the range C3:C5 by pressing the key “CTRL+V” on the keyboard.

 

img4

 

Paste Special: You can use the Paste Specialcommand to paste the specific cell content or attributes such as formula, formatting, comments etc.

  • Select the range C2:C5 and copy by pressing the key “CTRL+C” on the keyboard, then right click on the mouse, select “Paste Special” from the popup.
  • The dialog box will appear, click on “Values”, Click on OK.

Note:- If you want to return the retirement age, you just need to replace the today date from retirement date calculation will work to calculate the retirement age.

 



15 thoughts on “How to calculate age from date of birth in Excel

  1. When inputting a birthdate that is before 1/1/1930 (i.e. 12/31/29), excel defaults the year as 2029. Why is this and is there a fix or a setting?

  2. “Hi Chris,

    That is nothing to do with Excel directly.

    You have your computer set up to make that assumption, and Excel has followed your instructions accordingly.

    Check out your date settings under Regional Settings.

    Alan.”

  3. “From Excel help:

    When you enter a date in a cell and you enter only two digits for the year, Excel interprets the year as follows:

    The years 2000 through 2029 if you type 00 through 29 for the year. For example, if you type 5/28/19, Excel assumes the date is May 28, 2019.

    The years 1930 through 1999 if you type 30 through 99 for the year. For example, if you type 5/28/98, Excel assumes the date is May 28, 1998.
    If you are using Microsoft Windows 98 or Microsoft Windows 2000, you can, without the assistance of your system administrator, change the way two-digit years are interpreted.”

  4. “Dear Sirs,
    By using the formula =INT((TODAY()-A1)/365.25), I am getting a whole number as age (e.g. 40).
    Now if I want to have the number of month also (Years+Months e.g. 40.3) and when I want to merge with word, the same age will appear with one decimal only (e.g. 40.3) and not fifteen (15) decimals (as 40.29847562534987)
    Which formula I have to use.
    Many Thanks.”

  5. “Hi Rebouche,

    Please can you clarify a little.

    The problem is that a month is not a well defined period of time (can be anything from 28 days to 31 days inclusive).

    Therefore, it is difficult to use a ‘month’ as a measure of time (as opposed to an absolute time reference for which it is very well defined).

    The same problem arises for a ‘year’ – as discussed above. A year can be either 365 or 366 days, and is therefore, not well defined either as a measure of time. What exactly would you mean by 0.3 of a year?

    Excel has functions that will ‘standardise’ those period, and resolve the issue that you are encountering by assuming that every year has 360 days, and every month exactly 30 days. See Excel help on the DAYS360 function.

    In general, I would suggest that you steer clear of representing someones age as “”40.3″” if at all possible. Giving the age to three significant figures, implicitly leads the reader to assume a level of accuracy that may not be borne out of you ask a selection of people what it means.

    If you have to do this, then you need to define your units explicitly for the reader of the report (and other users of the spreadsheet model) before you start so that everyone knows exactly what you mean.

    Does that help? Post back if you want more help, but I suggest that the 360 day year has to be the way to go for you, even though it leads to errors close to a person’s birthday.

    Alan.”

  6. “Hi Alan,
    Many thanks for your answer.
    It is mail merge between Word and Excel, and my target is to select people using age with one decimal only (as mentioned already).
    For example, When I am inserting the date of birth in A1(which is = 14 June 1962) and in B1 (=NOW()) and in another cell, I am using the follwing formula: =IF(A1=0;””””;((B1-A1)+1/365). I am getting the following result 41.25018031 formatted in number with one decimal only 41.3.
    The problem is when I am reporting this age to my document (Word) the result is as follow: 41.2501533878744171 but I need only to have 41.3 nothing else … Can you help to resolve this problem by keeping at least the same formula mentioned above.
    Thanks “

  7. “Hi Rebouche,

    If you change your formula to:

    =Round(IF(A1=0;””””;((B1-A1)+1/365),1)

    then this will change the actual value to be rounded to one decimal place.

    Does that work for you?

    Alan.”

  8. “Hi Rebouche,

    You are right – it doesn’t!

    I copied your formula from your post, and just put the ROUND outside of it. I should have done it in Excel and pasted to here:

    =IF(A1=0,””””,ROUND(((B1-A1)+1)/365,1))

    Does that work?

    Alan.”

  9. “Hi Alan,
    Using your corrected formula, and when I pass to my document, I am always getting 15 (fifteen) decimals instead of (1) one.
    Meawhile, I replaced the (,) by (;).
    Thanking you,
    Rebouche”

  10. “Hi Rebouche,

    I am not familiar with mail merge, but perhaps someone else will be able to help you with why it changesthe value back to fifteen decimals.

    The value returned by the formula will either be “””” (blank) or will be a number to one decimal place.

    However, as a workaround, you could perhaps copy and paste the answers to values, thus removing the formulae, and leaving only the values (to one decimal place), before you mail merge.

    Would that work for you?

    Alan.

  11. Hi. I want excel to work out what someone’s age will be at an event on 9 August 2014, in order to assist allocating kids to their correct age group activities. Thanks

  12. you can use the following function for calculating the correct age in years, months and days.
    =DATEDIF(A1,A2,”y”) & “years, ” & DATEDIF(A1,A2, “ym”) & ” months, ” & DATEDIF(A1,A2, “md”) & ” days “

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>