How to Calculate Age in Excel


Home >> How to Use Excel Tutorial >> Excel Tips and Trick >> How to Calculate Age in Excel



In this tutorial, you will learn how to calculate age in excel. The understanding of how to calculate age from date of birth in excel is sometimes needed for various purposes. The approach to do that which is discussed here is generally by using DATEDIF. If you don’t understand this formula, you should learn it first in another Compute Expert’s tutorial.


Why do We Need to Learn How to Count Age in Excel?


In the data processing process on a spreadsheet file, sometimes we need to know how to find age in excel. This is needed because we might want to process data related to the birthday of someone. By understanding how to calculate age from dob in excel, the process will be much easier to do.

The case example when this is needed in work is when we process specific data for marketing purposes. To divide market segments based on age, for example, maybe we have the birthday data from our potential customers. If we want to segment correctly, then we need to compare those data with today’s date. If there are many potential customers’ data to be processed, then we should understand how to calculate age in excel automatically. The knowledge will make the calculation process becomes much faster and more accurate.

Because of that, you need to learn how to calculate age in excel automatically. If you often work on data related to birth date in your spreadsheet file, then the understanding becomes much more important.


What is How to Calculate Age in Excel?


How to calculate age in excel is a function to calculate age based on the birth and another specific dates comparison. The way of how to compute age in excel is done by using the date difference calculation formula, DATEDIF.


How to Get Age in Excel?


The following will explain how to calculate age in excel through the use of three methods. We divide it into these three methods because usually, there are three types of number forms needed for the calculation result. These three are: only the years, with the months and days decimal, and the full writing of years, months, and days. If you can, learn all three so you can use whatever method you need later.


Method 1: Only the Years


  1. Type equal ( = ) in the cell where you want to put the calculation result

    How to Calculate Age in Excel - Screenshot of Step 1-1

  2. Type DATEDIF (can be with large and small letters) and open bracket sign after =

    How to Calculate Age in Excel - Screenshot of Step 1-2

  3. Input the birth date using the DATE formula or the cell coordinate where the date is after the open bracket sign. Then type a comma ( , )

    How to Calculate Age in Excel - Screenshot of Step 1-3

  4. Input the current or another date for the basis of the calculation. Input it using DATE (or just TODAY if you use today’s date) or with the cell coordinate where the date is after the comma. Then type a comma

    How to Calculate Age in Excel - Screenshot of Step 1-4

  5. Input a “y” letter between quotation marks (“”) (can be with a large or small letter) or the cell coordinate where the letter is

    How to Calculate Age in Excel - Screenshot of Step 1-5

  6. Type close bracket sign

    How to Calculate Age in Excel - Screenshot of Step 1-6

  7. Type Enter
  8. The process is done!

    How to Calculate Age in Excel - Screenshot of Step 1-8




Method 2: The Decimal of Months and Days


  1. Type equal ( = ) in the cell where you want to put the calculation result

    How to Calculate Age in Excel - Screenshot of Step 2-1

  2. Type DATEDIF (can be with large and small letters) and open bracket sign after =

    How to Calculate Age in Excel - Screenshot of Step 2-2

  3. Input the birth date using the DATE formula or the cell coordinate where the date is after the open bracket sign. Then type a comma ( , )

    How to Calculate Age in Excel - Screenshot of Step 2-3

  4. Input the current or another date for the basis of the calculation. Input it using DATE (or just TODAY if you use today’s date) or with the cell coordinate where the date is after the comma. Then type a comma

    How to Calculate Age in Excel - Screenshot of Step 2-4

  5. Input a “y” letter between quotation marks (“”) (can be with a large or small letter) or the cell coordinate where the letter is

    How to Calculate Age in Excel - Screenshot of Step 2-5

  6. Type close bracket sign and plus sign ( + )

    How to Calculate Age in Excel - Screenshot of Step 2-6

  7. Repeat steps 2 to 4. Then input “yd” letters using quotation marks or a cell coordinate

    How to Calculate Age in Excel - Screenshot of Step 2-7

  8. Type close bracket sign then a slash ( / ). Then, type in 365 as a symbol of the division using the number of days in 1 year

    How to Calculate Age in Excel - Screenshot of Step 2-8

  9. Type Enter
  10. The process is done!

    How to Calculate Age in Excel - Screenshot of Step 2-10




Method 3 - INT


  1. Type equal ( = ) in the cell where you want to put the calculation result

    How to Calculate Age in Excel - Screenshot of Step 3-1

  2. Type DATEDIF (can be with large and small letters) and open bracket sign after =

    How to Calculate Age in Excel - Screenshot of Step 3-2

  3. Input the birth date using the DATE formula or the cell coordinate where the date is after the open bracket sign. Then type a comma ( , )

    How to Calculate Age in Excel - Screenshot of Step 3-3

  4. Input the current or another date for the basis of the calculation. Input it using DATE (or just TODAY if you use today’s date) or with the cell coordinate where the date is after the comma. Then type a comma

    How to Calculate Age in Excel - Screenshot of Step 3-4

  5. Input a “y” letter between quotation marks (“”) (can be with a large or small letter) or the cell coordinate where the letter is

    How to Calculate Age in Excel - Screenshot of Step 3-5

  6. Type close bracket sign and and sign ( & ). Then the word y in quotation marks. Then type an and sign again

    How to Calculate Age in Excel - Screenshot of Step 3-6

  7. Repeat steps 2 to 4. Then type “ym” letters using quotation marks or a cell coordinate

    How to Calculate Age in Excel - Screenshot of Step 3-7

  8. Type close bracket sign and and sign ( & ). Then the word m in quotation marks. Then type an and sign again

    How to Calculate Age in Excel - Screenshot of Step 3-8

  9. Repeat steps 2 to 4. Then type "md" letters using quotation marks or a cell coordinate

    How to Calculate Age in Excel - Screenshot of Step 3-9

  10. Type close bracket sign and and sign ( & ). Then input the word d in quotation marks

    How to Calculate Age in Excel - Screenshot of Step 3-10

  11. Type Enter
  12. The process is done!

    How to Calculate Age in Excel - Screenshot of Step 3-12



Exercise


After learning how to calculate age in excel using the three methods, now is the time to do an exercise. The exercise is done so you can deepen your understanding of how to calculate age in excel using the three methods!

Download the exercise file of how to calculate age in excel and answer all the questions. Download the answer key file too if you have done the exercise and want to check your answers. Or probably you are confused on how to finish the exercise!

Link to the exercise file on how to calculate age in excel:
Download here

Questions:

  1. How many years old is each of the people? We need only the years’ number in here and the basis for the calculation is today’s day
  2. How many years old is each of the people? We need the decimal of months and days in here and the basis for the calculation is today’s day
  3. How many years old is each of the people? We need the number of years, months, and days in here and the basis for the calculation is 31 December 2020

Link of the answer key file for the exercise on how to calculate age in excel:
Download here


Additional Note


If you need the “year” word in method 1 and 2 results, add using the appropriate steps given in method 3.



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on


CONTACT US PRIVACY POLICY TERMS AND CONDITION AFFILIATE DISCLOSURE @Compute Expert