Download Article
Quick tutorial for finding someone's age in Excel
Download Article
Trying to figure out someone's age using Excel? You can calculate age by using functions and dates. The DATEDIF and DAYS function will calculate the difference between two specified dates. The YEAR and YEARFRAC functions work well if you just need the age in years. You can also use the DATE function to calculate the date someone will turn a certain age. [1] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source This wikiHow guide will show you how to calculate age using Microsoft Excel.
Things You Should Know
- Use the DATEDIF function to calculate the time between someone's birth date and another date. [2] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
- You can calculate how old someone is in years, months, or days using DATEDIF.
- YEAR and YEARFRAC return the number of years between two dates.
- Use the DATE function to find out what date someone will turn a certain age. [3] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
Steps
-
Create a "Name" column and a "Birthday" column. These columns will allow you to keep track of the age for a list of people. The "Birthday" column will contain a birthdate for each individual.
-
Enter the birthdates using a common format. Make sure each birthday is entered using the same format, such as MM/DD/YYYY. Excel should automatically detect that you are entering dates, and will format the data accordingly. [4] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
- If the data is being auto-formatted as something else, highlight the cells and click the drop-down menu in the "Numbers" section of the Home tab. Select "Short Date" from the list of options.
- If you need to change the date to a different format (like DD/MM/YYYY):
- Go to Home and click the Number Format button in the Numbers section.
- Go to the Date tab and change "Locale" to the country with the correct formatting.
Advertisement -
Create an "Age" column. This column will display the age for each entry after you enter the formula.
-
Select the first empty cell in the "Age" column. This is where you'll enter the formula to calculate age.
-
Type the YEAR formula to calculate age in years. The YEAR function returns the year of a given date. [5] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source This formula subtracts the current year from the birth year. Type the following into the cell: [6] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
- =(YEAR(NOW())-YEAR(B2))
- Replace B2 with the cell containing the birth date.
- Press ↵ Enter to confirm the formula and calculate the age.
- You can click and drag the small square down to copy the formula to the cells below.
Advertisement
-
Create a "Name" column and a "Birthday" column. These columns will allow you to keep track of the age for a list of people. The "Birthday" column will contain a birthdate for each individual.
-
Enter the birthdates using a common format. Make sure each birthday is entered using the same format, such as MM/DD/YYYY. Excel should automatically detect that you are entering dates, and will format the data accordingly.
- If the data is being auto-formatted as something else, highlight the cells and click the drop-down menu in the "Numbers" section of the Home tab. Select "Short Date" from the list of options.
- If you need to change the date to a different format (like DD/MM/YYYY):
- Go to Home and click the Number Format button in the Numbers section.
- Go to the Date tab and change "Locale" to the country with the correct formatting.
-
Create an "Age" column. This column will display the age for each entry after you enter the formula.
-
Select the first empty cell in the "Age" column. This is where you'll enter the formula to calculate age.
-
Type the YEARFRAC formula to find the age in years. This function calculates the fraction of the year between the specified dates. For example, the fractional years between 1/1/1990 and 3/25/2023 is 33.23. [7] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source This indicates there are 33 years and 23 percent of a year between the dates. To find an age, use this formula: [8] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
- =YEARFRAC(B2,TODAY(),1)
- Replace B2 with the cell containing the birth date.
- The 1 indicates that the function should use the actual day count, instead of a different standard like 30/360.
- Press ↵ Enter to confirm the formula and calculate the age.
- You can click and drag the small square down to copy the formula to the cells below.
Advertisement
-
Create a "Name" column and a "Birthday" column. These columns will allow you to keep track of the age for a list of people. The "Birthday" column will contain a birthdate for each individual.
- This is great for creating a database of ages for a group of people using Excel . You could then use VLOOKUP to quickly find a specific person's age.
-
Enter the birthdates using a common format. Make sure each birthday is entered using the same format, such as MM/DD/YYYY. Excel should automatically detect that you are entering dates, and will format the data accordingly. [9] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
- If the data is being auto-formatted as something else, highlight the cells and click the drop-down menu in the "Numbers" section of the Home tab. Select "Short Date" from the list of options.
- If you need to change the date to a different format (like DD/MM/YYYY):
- Go to Home and click the Number Format button in the Numbers section.
- Go to the Date tab and change "Locale" to the country with the correct formatting.
-
Create an "Age" column. This column will display the age for each entry after you enter the formula.
-
Select the first empty cell in the "Age" column. This is where you'll enter the formula to calculate age.
-
Enter the formula for calculating the age in years. Type the following formula, which assumes that the first birthday is listed in cell B2: [10] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
- =DATEDIF(B2,TODAY(),"Y")
- DATEDIF is a function that calculates the difference between two dates. (B2,TODAY(),"Y") tells DATEDIF to calculate the difference between the date in cell B2 (the first birthday listed) and the current date ( TODAY() ). It outputs the calculation in years ( "Y" ).
- If you'd rather see the age in days or months, use "D" or "M" instead.
- If you want to calculate someone's age on a specific date, change TODAY() to a cell reference containing that date.
-
Click and drag the square in the bottom-right corner of the cell down. This will copy and apply the same formula to each line, adjusting it accordingly so that the correct birthday is calculated.
- You're done! Now you can create a graph of ages or perform data analytics on the dataset.
-
Troubleshoot a formula that isn't working. If the formula is displaying something like #VALUE! or #NAME? , then there is likely an error somewhere in the formula. Make sure that the syntax is exactly correct, and that you are pointing to the correct cells in the spreadsheet. Note that the DATEDIF() formula does not work for dates before 01/01/1900.
Advertisement
-
Create a "Name" column and a "Birthday" column. These columns will allow you to keep track of the age for a list of people. The "Birthday" column will contain a birthdate for each individual.
-
Enter the birthdates using a common format. Make sure each birthday is entered using the same format, such as MM/DD/YYYY. Excel should automatically detect that you are entering dates, and will format the data accordingly.
- If the data is being auto-formatted as something else, highlight the cells and click the drop-down menu in the "Numbers" section of the Home tab. Select "Short Date" from the list of options.
- If you need to change the date to a different format (like DD/MM/YYYY):
- Go to Home and click the Number Format button in the Numbers section.
- Go to the Date tab and change "Locale" to the country with the correct formatting.
-
Create an "Age" column. This column will display the age for each entry after you enter the formula.
-
Select the first empty cell in the "Age" column. This is where you'll enter the formula to calculate age.
-
Use the DAYS formula to calculate age. This function returns the number of days between two dates. To find someone's age today: [11] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
- =DAYS(TODAY(),B2)
- Replace B2 with the cell containing the birth date.
- Press ↵ Enter to confirm the formula and calculate the age.
- You can click and drag the small square down to copy the formula to the cells below.
Advertisement
-
Create a "Name" column and a "Birthday" column. These columns will allow you to keep track of the age for a list of people. The "Birthday" column will contain a birthdate for each individual. This method calculates the date someone will turn a certain age.
-
Enter the birthdates using a common format. Make sure each birthday is entered using the same format, such as MM/DD/YYYY. Excel should automatically detect that you are entering dates, and will format the data accordingly. [12] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
- If the data is being auto-formatted as something else, highlight the cells and click the drop-down menu in the "Numbers" section of the Home tab. Select "Short Date" from the list of options.
- If you need to change the date to a different format (like DD/MM/YYYY):
- Go to Home and click the Number Format button in the Numbers section.
- Go to the Date tab and change "Locale" to the country with the correct formatting.
-
Create a "Future Date" column. This column will contain the formula for calculating what date it will be when someone turns a specific age.
-
Use the DATE function to calculate the date. The DATE function uses a year, month, and day to calculate the date at that specified time. You can use the function to find the date someone turns a certain age by adding that many years to their birth year. For example, to calculate when someone will turn 20: [13] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
- =DATE(YEAR(B2)+20, MONTH(B2), DAY(B2))
- This assumes the birthdays are in column B.
- Change the "20" to another age to calculate a different date.
Advertisement
Expert Q&A
Search
-
QuestionOnce I have the corretc results, How can I have the average age?Kyle Smith is a wikiHow Technology Writer, learning and sharing information about the latest technology. He has presented his research at multiple engineering conferences and is the writer and editor of hundreds of online electronics repair guides. Kyle received a BS in Industrial Engineering from Cal Poly, San Luis Obispo.You can use the AVERAGE function to calculate the average age of the list of ages you created. For example, if the ages are in column B rows 2 through 20, you would write =AVERAGE(B2:B20).
-
QuestionDoes the formula to calculate age work for a person born in 1929 or earlier?Community AnswerI've tested the formula using Excel 2013 and it worked correctly. Here is the formula I used: =DATEDIF(A2,TODAY(),"Y") where A2 was the cell with the birth date. I also used the formula as follows: =DATEDIF(A6,"31-dec-2015","Y") where A6 was the birth date and I wanted the age as of December 31, 2015; this also calculated correctly. However, the formula will not work if the date is before January 1, 1900.
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement
Sample Calculator
Video
Tips
- The "DATEDIF" function is not listed in the function wizard. You can use the "YEAR" function to return age in years, but it does not take the months and days of your dates into account.Thanks
Submit a Tip
All tip submissions are carefully reviewed before being published
Name
Please provide your name and last initial
Thanks for submitting a tip for review!
Advertisement
References
- ↑ https://support.microsoft.com/en-us/office/date-and-time-functions-reference-fd1b5961-c1ae-4677-be58-074152f97b81
- ↑ https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c
- ↑ https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349
- ↑ https://support.microsoft.com/en-us/office/calculate-age-113d599f-5fea-448f-a4c3-268927911b37
- ↑ https://support.microsoft.com/en-us/office/year-function-c64f017a-1354-490d-981f-578e8ec8d3b9
- ↑ https://support.microsoft.com/en-us/office/calculate-age-113d599f-5fea-448f-a4c3-268927911b37
- ↑ https://support.microsoft.com/en-us/office/calculate-age-113d599f-5fea-448f-a4c3-268927911b37
- ↑ https://support.microsoft.com/en-us/office/yearfrac-function-3844141e-c76d-4143-82b6-208454ddc6a8
- ↑ https://support.microsoft.com/en-us/office/calculate-the-difference-between-two-dates-8235e7c9-b430-44ca-9425-46100a162f38
- ↑ https://support.microsoft.com/en-us/office/calculate-the-difference-between-two-dates-8235e7c9-b430-44ca-9425-46100a162f38
- ↑ https://support.microsoft.com/en-us/office/calculate-age-113d599f-5fea-448f-a4c3-268927911b37
- ↑ https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349
- ↑ https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349
About This Article
Thanks to all authors for creating a page that has been read 598,983 times.
Advertisement
