How to calculate age in Excel

67
48078
How to calculate age in Excel
Excel Spreadsheets

There are certain activities in Excel that are often performed while working with spreadsheets, and in this case there is a need to automate them where possible to increase the productivity of the jobs performed.

An example of this is the age calculation in Excel. And for those who work with people records or even calculations of days, it is necessary to optimize this operation in order to avoid that the whole manual process is done besides avoiding possible errors. Thinking through this subject, we separate a step by step so you can learn how to calculate age in Excel in a practical and efficient way. Follow us!

Knowing the functions

The calculation of ages may be simple for some people, but have you ever imagined having a spreadsheet with users' records with their respective dates of birth and their ages? In this case, there will be work on having to update the dates daily as people pass anniversaries over the days.

Excel Spreadsheets

However, to avoid all this work and automate this task, it is enough to make use of two simple functions, being INT and NOW.

  • INT function

This function basically weights a number.

Syntax: INT (num)

Where:

On one: represents the number or reference of a cell to be rounded.

  • Function Now

The NOW function is intended to display the current time and date (depending on the cell format), which is very useful when you want to perform calculations with dates and is recalculated automatically whenever the worksheet is reopened.

Syntax: NOW ()

Calculating Ages

Now that we know the details of the functions that will integrate the calculation of age, follow below a practical example where there is a table with dates of birth, current date and a column that will receive the calculation of the age.

How to calculate age in Excel

In the "Current Date" column we will use the AGORA () function to insert the current date and update it whenever we open the worksheet in the later days.

How to calculate age in Excel

At the end of the function just hit enter and check the result.

How to calculate age in Excel

To apply the same function to the other cells, simply use the fill handle.

How to calculate age in Excel

How to calculate age in Excel

In the next step, we will use the INT function to calculate the difference between the date of birth and the current date. In this way, we will have an integer value that represents the age. See below:

How to calculate age in Excel

Note that we are performing a subtraction of the current date with the date of birth in which it will bring an integer value. This result will be divided by 365,25 due to leap years.

At the end of the operation, just hit enter and check the result.

How to calculate age in Excel

To replicate the function to the other cells, we will use the auto-fill handle again

How to calculate age in Excel

How to calculate age in Excel

We can also test the automatic updating of the ages, for example by changing a date of birth.

How to calculate age in Excel

In this way, we have a calculation of dates always updated according to the dates and without the need to carry it out frequently.

Based on the tips mentioned here, it was possible to know the step by step to perform the automatic calculation of ages. Therefore, with the use of this feature it is possible to optimize this calculation by having the integration of an automatic function.

Did you like the tip? Check out other Excel tips at Our site!

Excel Spreadsheets

67 COMMENTS

  1. I need to calculate for age of years and months, for children, years I got it but I have difficulty in the months.
    Can someone help me?

  2. Hi Alexandre, in theory should work correctly without problems. If you got an error, I suggest you evaluate the formula to know where the error might be being generated - go to the FORMULAS tab and use the formula evaluation feature.

  3. Hi Analice, go to the FORMULAS tab and use the evaluate formula functionality to understand where the error is generated. Then just correct

  4. Hi I'm trying on the current date of the RIGHT… NOW FOR AGE I APPEARED SO: # VALUE, I DON'T UNDERSTAND AGE .. SOMEONE COULD HELP ME?

  5. Good morning
    When I make the age account 05 / 01 / 1979 for 31 / 08 / 2019 the value is 41 and the correct and 40 how do I fix this formula?

  6. Hi Bruna, how are you? If the admission date is in A1 for example, just use = TODAY () - A1

    This will give you the amount of days he has had since joining the company. Then you can divide this value by 365 to have the number of years, by 30 to have the months, if you use the formula explained in the post is fine, but if you want to simplify without having the exact data, just do what I suggested on here

  7. Hello, okay?

    How do I get the time from home from employees?
    I added the date they entered and also the current date, but when I put the formula it does not accept.

    I need to get years, months, and days that collaborators are completing.

  8. You can use the fill handle (bottom right corner of the cell) to drag the formula across the spreadsheet - you can copy and paste as well

  9. How to enforce all banknotes instead of putting the formula one by one?

  10. Hi Francisco, I do not quite understand, but in theory the formula for calculating age already takes into account the date of birth, so it changes according to the current date

  11. Good afternoon, I'm having problems with the formula, I need her to give me her age and that on her birthday, she'll change too.
    Ex: The athlete on the day of the event is birthday and would have to change the age to change the category.

  12. Assuming the date of birth is in the cell A1.
    There is a way to do the correct age calculation with the datadif function:
    DATADIF (A1; TODAY (); "Y");

    I did the test and it worked out for all dates during 3 years.
    The calculation that divides the number of days by 365,25 gets a value with 1 difference for dates with day and month next to the day and month of the date in question.

  13. I tested the method suggested by Valdino and realized that for some dates it was wrong so I made some modifications and got the following formula.
    Assuming the date is in the A1 cell, paste the following formula in the cell where you want the result:
    =SE(MÊS(A1)>MÊS(AGORA());ANO(AGORA())-ANO(A1)-1;SE(MÊS(A1)=MÊS(AGORA());SE(DIA(A1)>DIA(AGORA());ANO(AGORA())-ANO(A1)-1;ANO(AGORA())-ANO(A1));ANO(AGORA())-ANO(A1)))

    Then format the cell as number to see the result correctly

  14. Without seeing the formula you used and the data is hard to say what could be done wrong, but I would try to follow exactly the same example of the blog to try to arrive at the same result and then, being all right, just change the dates

  15. Good morning someone could help because I am typing the formula and this appears 01 / 01 / 1990 and I need to calculate the age of the company that the person works years, months and days. Eg: 07 years, 4 months and 8 days.

  16. A solution I like to teach because it has no problems with rounding or decimals, and, on top of that, it teaches the SE formula, it is checking if the month of birth added to the day of birth is greater than the actual month added to the current day. If it is, we subtract the year of birth from the current year, and since the person has not yet made a birthday in the current year, we subtract more 1. Otherwise, we only subtracted the year of birth of the current year, since the person has already celebrated his birthday in the current year. There is no mistake. See below, assuming the date of birth is in cell A1:

    =SE(MÊS(A1)+DIA(A1)>MÊS(AGORA())+DIA(AGORA());ANO(AGORA())-ANO(A1)-1;ANO(AGORA())-ANO(A1))

  17. Hi Andrezza, to find out where the error is go in the FORMULAS tab and choose the option to evaluate formula to see every calculation where the error occurs.

  18. Hi everyone, I also researched this topic to help a HR person, here at work and I found this post.
    There is another very simple way to calculate Age with the TEXT () function, I like to use it to calculate time too!
    The very simple Syntax: = TEXT (NOW () - "DATA_NASCIMENTO"; "A") this way it returns the years, if you want to return the months you can also insert the "M" or "MM" clause to add 0 , it looks like this: = TEXT (NOW () - "DATA_NASCIMENTO", "A, MM") thus returns the year and months in decimal form.

  19. Hi Carlos, it is important to understand the formula you are using and the formatting of the marked values, but if the problem is only the least, put in the formula a new ABS (absolute) function, a minus sign or multiply the final value by - 1, will make it positive

  20. I calculated the age of a person born in 21 / 01 / 1990 and the result is always at least age (-). How can I change this ????? Ex: -29

  21. Ball show this tip !!!

    Linguajar very simple, easy to understand and with a sensational result!

    Thanks!!

  22. Here is my solution for differentiation from day to day, month to month and year to year.

    = CONCATENATE (
    SE (DATADIF (Start Date; TODAY (); "y") = 1;
    DATADIF (Initial Date, TODAY (), "y") & "ano,";
    DATADIF (Initial Date, TODAY (), "y") & "years,");
    SE (DATADIF (Start Date; TODAY (); "ym") = 1;
    DATADIF (Start Date; TODAY (); "ym") & "month e";
    DATADIF (Initial Date, TODAY (), "ym") & "months e");
    SE (DATADIF (Initial Date; TODAY (); "md") = 1;
    DATADIF (Initial Date; TODAY (); "md") & "day";
    DATADIF (Initial Date, TODAY (), "md") & "days")
    )

  23. The DATADIF function proposed by Gustavo Lima is more accurate. The method of dividing by 365,25 and truncating is approximate. For example, for 15 / 8 / 17 birth date and today's date 15 / 8 / 18, DATADIF gives 1 year of age, which is correct, but the division method gives zero, which is wrong.

  24. Hi Karla, I did not understand what mistake this could be. I just tested here and it worked perfectly - = INT ((D2-C2) / 365,25). What you can do differently is to lock C2 in a single cell, since the date will always be the same.

  25. Hello!
    I need to know how old the person will be on 19 / 04 / 2018 (fixed to everyone on the list). I tried to use the suggested formulas by putting the date, but it presents an error.
    Note: I can not create an extra column in the worksheet.

  26. Thanks for the words Douglas! Big hug and if you need to ask any questions, we are here

  27. Many thanks, Rafael! Helped me a lot! Keep up the great work! A hug!

  28. Good evening I'm trying to calculate my age and I'm not even following the form above. weight help.

  29. Hi Ron, I suggest following the step by step indicated in another comment here. Where you can pick up the amount of years and turn into days or months and manipulate the result that appears.

  30. Hi Ron, I do not have not. It is worth trying this step by step, but as I told our reader, I did not test.

  31. Do you have the formula?

    I believe that to give to put in the same cell this formula!

  32. To put an 0 before for those who have less than 10 years ago I did so:

    =CONCATENAR(SE((INT(AGORA()-B5)/365,25)<10;"0";"");INT(((AGORA()-B5)/365,25)))

    If anyone has the formula to put months to those who have less than 01 year, thank you.

  33. Thank you, it worked perfectly! I just made a change, I used the formula NOW () in the calculation, stayed = INT ((NOW () - A2) / 365,25)

  34. Hi Sueli, actually the important date that needs to be in the cell is the date of birth. The current date you can put in the formula itself, without the need for the auxiliary column.

  35. Hello,

    Is it possible to do this calculation using a cell with the current date, without having to fill an entire column with that information?

  36. Simply change the cell number format. Instead of leaving formatted as date, change to general or number without decimals.

  37. How to correct age because when giving enter the age appeared in date format so 28 / 01 / 1900 how to solve this problem. thank you

  38. Hi Melanie, I never tried to make the bill this way no. One possible test to do, but one that has the inconsistency of leap years and months with more or fewer days is to do this step by step:

    1 - use two cells, one with the date of birth and one with the current date
    2 - get current date and subtract from date of birth
    3 - in another cell get this value and divide by 365 (VALUE 1)
    4 - round down VALUE 1 (that's the age in years)
    5 - Now, take the difference between 1 VALUE and age in years, multiply by 365 and divide by 30 (2 VALUE)
    6 - round down the VALUE 2 (that's the age in months)
    7 repeat the process for days (but here is where the inconsistencies remain because of the over or under days in leap years and months with 28, 29, 30 or 31 days)

  39. Hello, I need to calculate the age of a person in years, months and days. Eg: 07 years, 4 months and 8 days.
    How do I do that?

  40. You can use a conditional SE function. Something of type = SE (cell of date of birth = ""; "" age function)

  41. Dear,
    when the cell of the birth date is empty, the cell that would return the age returns the 117 number. What do you do for that 117 number does not appear?
    Thank you!

LEAVE AN ANSWER

Please, write your comment
Please enter your name