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
-
Type equal ( = ) in the cell where you want to put the calculation result
-
Type DATEDIF (can be with large and small letters) and open bracket sign after =
-
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 ( , )
-
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
-
Input a “y” letter between quotation marks (“”) (can be with a large or small letter) or the cell coordinate where the letter is
-
Type close bracket sign
- Type Enter
-
The process is done!
Method 2: The Decimal of Months and Days
-
Type equal ( = ) in the cell where you want to put the calculation result
-
Type DATEDIF (can be with large and small letters) and open bracket sign after =
-
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 ( , )
-
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
-
Input a “y” letter between quotation marks (“”) (can be with a large or small letter) or the cell coordinate where the letter is
-
Type close bracket sign and plus sign ( + )
-
Repeat steps 2 to 4. Then input “yd” letters using quotation marks or a cell coordinate
-
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
- Type Enter
-
The process is done!
Method 3 - INT
-
Type equal ( = ) in the cell where you want to put the calculation result
-
Type DATEDIF (can be with large and small letters) and open bracket sign after =
-
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 ( , )
-
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
-
Input a “y” letter between quotation marks (“”) (can be with a large or small letter) or the cell coordinate where the letter is
-
Type close bracket sign and and sign ( & ). Then the word “y” in quotation marks. Then type an and sign again
-
Repeat steps 2 to 4. Then type “ym” letters using quotation marks or a cell coordinate
-
Type close bracket sign and and sign ( & ). Then the word “m” in quotation marks. Then type an and sign again
-
Repeat steps 2 to 4. Then type "md" letters using quotation marks or a cell coordinate
-
Type close bracket sign and and sign ( & ). Then input the word “d” in quotation marks
- Type Enter
-
The process is done!
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:
- 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
- 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
- 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.
