Calculate Someone's Age in Excel

Add to Favorites
Author: | Edits: don

How to automatically calculate someone's age using Excel.  This method is simple and will update every year so that you never have to manually adjust an age in Excel.

This is great when you are working with employee or customer data.

I'll show you two methods to do this; which one you use depends on the setup of your data.

Sections:

Calculate Someone's Age When you Have the Year of Their Birth

Calculate Someone's Age When you Have the Date of their Birth

Notes

Calculate Someone's Age When you Have the Year of Their Birth

=YEAR(TODAY())-A2

This method assumes that you have a plain number for the year that the person was born in its own cell.

  1. Type =YEAR(TODAY())- into a cell.  Note the minus sign after the last closing parenthesis.
    1853e2f8d447f248a341f8d2817e05bb.png
  2. Select the cell that contains the year the person was born.
    fb8d941546d984ba2eae1bfd343cd948.png
  3. Hit Enter and that's it.
    51da655dc7c8a0421540eaa2e9db92f2.png

Calculate Someone's Age When you Have the Date of their Birth

=YEAR(TODAY())-YEAR(A3)

This method assumes that you have the actual date the person was born in a cell. This is perhaps the more common way of keeping track of a person's information.

  1. Type =YEAR(TODAY())- into a cell (just like in the first example).  Make sure to include the minus sign after the last parenthesis.
    bdcd14b814b2de4fe87b986d90ca4fab.png
  2. Continue in the same cell typing YEAR(
    ec8fd81bb40b000c1665d0607b8c3ce9.png
  3. Select the cell that contains the date of the person's birth and then type a closing parenthesis.
    903a42cc00f61c19eaf8a60f00aab01c.png
  4. Hit Enter.
    ca464af16ccef936f2e35b148ca1b0ad.png
  5. Excel might format the result as a date, like it did for me and, in that case, we need to format the cell as a regular number or just as General (Home tab > Number section > Select General from the drop-down menu).
    298eb4df00fded9bea965e71f7650bc7.png
  6. That's it!
    a67c9be63199874544ed294a8a5f33ca.png

Notes

This is a great technique for getting someone's age without much hassle.  Even though it looks complicated, we are just using the YEAR function to get the year from the TODAY function, which returns the current date, and, in the second example, using the YEAR function to get the year from the person's birth date.

Check out our other date and time tutorials for Excel.

Make sure to download the accompanying Excel file so you can follow along with this tutorial and get a better understanding of how this works.


Excel Function: TODAY(), YEAR()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
RATE Function - Calculate an Interest Rate in Excel
Tutorial: The Rate function in Excel (RATE) calculates the interest rate for a financial transaction...
Manually Calculate Formulas and Functions in Excel
Tutorial: How to force Excel to recalculate all formulas and functions without editing or entering ...
Calculate Hours Worked - Day or Night Shift & Breaks in Excel
Tutorial: Single simple formula to calculate the hours worked for a day shift or night shift and inc...
How to Calculate Hours Worked in Excel
Tutorial: This is a full time-tracking system that allows you to track how much time someone has wo...
NPER Function - Calculate Number of Periods Needed for a Set of Payments in Excel
Tutorial: How to calculate the number of periods required for an investment in order to get the desi...
Get the Number of Workdays Between Two Dates in Excel
Tutorial: How to calculate the total number of working days between two dates in Excel.  This allow...
Tutorial Details
Excel Function: TODAY(), YEAR()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course