How to Calculate Someone's Age in Excel Using Their Birth Date

The simplest way to calculate age in Excel

The fastest method is to use the DATEDIF function, which calculates the difference between two dates in years, months, or days. The formula is =DATEDIF(birth_date, TODAY(), "Y"), where "Y" means years. If your birth date is in cell A2, you would type =DATEDIF(A2, TODAY(), "Y") into any empty cell, and Excel returns the person's current age.

DATEDIF is built into Excel and works in both Windows and Mac versions. It automatically updates every day, so the age stays correct without you having to change anything. This is the method most people use because it requires only one line and handles leap years correctly on its own.

If DATEDIF does not work in your version of Excel, you likely have an older build. The alternative formula =INT((TODAY()-A2)/365.25) gives you the same result and works everywhere, though it is slightly less precise because it treats every year as 365.25 days rather than accounting for actual leap years.

Key Takeaways

Use =DATEDIF(A2, TODAY(), "Y") to calculate age in years from a birth date in cell A2.
DATEDIF updates automatically each day, so ages stay current without manual changes.
You can change the "Y" to "M" for months or "D" for days if you need age in a different unit.
If DATEDIF does not work, the formula =INT((TODAY()-A2)/365.25) produces the same result in older Excel versions.

Setting up your spreadsheet for age calculations

Start by putting all birth dates in a single column — usually column A or B — with a header row at the top. For example, put "Birth Date" in cell A1 and the actual dates starting in A2. Make sure the dates are formatted as dates, not text. If a date looks like "1/15/1985" or "January 15, 1985", Excel recognizes it. If it looks like "01151985" or "1985-01-15" without dashes, Excel may treat it as text and the formula will not work.

To check whether a date is formatted correctly, click on the cell and look at the formula bar at the top. If you see the date displayed normally, it is formatted as a date. If you see a long number like 30000, it is stored as a date but displayed as a number — right-click the cell, choose Format Cells, and select Date from the Category list.

Once your birth dates are in place, click on an empty cell in the next column — say B2 — and type your age formula there. You can then copy that formula down to all the other rows. Click B2, copy it (Ctrl+C on Windows or Cmd+C on Mac), select the range B3 to B100, and paste. Excel automatically adjusts the cell reference for each row.

Understanding what DATEDIF actually does

DATEDIF calculates the number of complete years between a birth date and today. If someone was born on March 10, 1990, and today is March 9, 2024, DATEDIF returns 33 because they have not yet had their 34th birthday. On March 10, 2024, it returns 34. This is why DATEDIF is more accurate than straightforward division — it counts actual birthdays, not just days elapsed.

The third part of the formula — the "Y", "M", or "D" — tells DATEDIF what unit to return. "Y" gives years, "M" gives the number of months beyond the last birthday, and "D" gives days beyond the last month. If you want to know someone's age as "34 years and 7 months", you would use two separate DATEDIF formulas: one with "Y" and one with "M".

DATEDIF uses TODAY(), which is a function that always returns the current date. Every time you open the spreadsheet or press F9 to recalculate, TODAY() updates. This means your ages are always current. If you want to calculate age as of a specific date instead — say, age on December 31, 2020 — replace TODAY() with that date in quotes, like =DATEDIF(A2, "12/31/2020", "Y").

When the alternative formula is useful

The formula =INT((TODAY()-A2)/365.25) works when DATEDIF is not available or when you are working in a spreadsheet program that is not Excel, such as Google Sheets or LibreOffice Calc. It subtracts the birth date from today, divides by 365.25 (accounting for leap years on average), and INT rounds down to the nearest whole number.

This formula is less precise than DATEDIF because it assumes every year is exactly 365.25 days. For most purposes, the difference is invisible — it might be off by a day or two around someone's birthday. But if you are calculating ages for a large group and need perfect accuracy, DATEDIF is the better choice.

You might also use this formula if you are building a spreadsheet that needs to work across multiple programs. Since DATEDIF is specific to Excel, the alternative formula is more portable. Test it in your target program first to make sure the TODAY() function works the same way.

Handling dates that are formatted as text

If your birth dates came from a form, a CSV file, or another source, they might be stored as text instead of as actual dates. Excel will not calculate with text dates — your formula will return an error like #VALUE!. To fix this, you need to convert the text to a real date first.

The easiest fix is to use the DATEVALUE function, which converts text that looks like a date into an actual date. If your text dates are in column A, use =DATEDIF(DATEVALUE(A2), TODAY(), "Y"). This tells Excel to treat A2 as a date even if it is stored as text. If you have many rows, you can also convert the entire column at once: select the column, go to Data > Text to Columns, click Next twice, and click Finish. Excel will reformat all the text dates as real dates.

Another common problem is dates in a format Excel does not recognize, like "15-Jan-1985" or "1985/01/15". If DATEVALUE does not work, try changing the date format in your source file before importing it into Excel. Most programs let you export dates in a standard format like MM/DD/YYYY.

Calculating age in months or days instead of years

If you need age in months, use =DATEDIF(A2, TODAY(), "M"). This returns the total number of months from birth to today. For a baby born 18 months ago, it returns 18. For age in days, use "D": =DATEDIF(A2, TODAY(), "D").

You can also combine these to show age in a more readable format, like "34 years and 7 months". Create two columns: one with =DATEDIF(A2, TODAY(), "Y") and another with =DATEDIF(A2, TODAY(), "YM"). The "YM" returns only the months beyond the last birthday. Then in a third column, use =B2&" years and "&C2&" months" to display them together.

For very young children, months or days might be more useful than years. Medical records often track age in months for children under two. If you are working with that data, the DATEDIF function with "M" or "D" gives you the precision you need without any extra steps.

Common mistakes and how to fix them

The most common error is typing the formula with the dates in the wrong order. DATEDIF always takes the earlier date first, then the later date. =DATEDIF(A2, TODAY(), "Y") is correct. =DATEDIF(TODAY(), A2, "Y") returns a negative number or an error. If you see a negative age, swap the order of the dates.

Another mistake is forgetting the quotes around the unit. =DATEDIF(A2, TODAY(), Y) without quotes returns an error because Excel thinks Y is a cell reference, not the letter Y. Always include the quotes: "Y", "M", or "D".

If your formula returns #NUM! or #VALUE!, the most likely cause is that one of the cells does not contain a valid date. Check that the birth date cell is formatted as a date and contains a real date, not text or a number. Click on the cell and look at the formula bar to see exactly what is stored there. If it shows a long number like 44000, it is a date stored as a number — format it as a date and the formula will work.

Frequently Asked Questions

Can I calculate age as of a date in the past instead of today?

Yes. Replace TODAY() with the date you want. For example, =DATEDIF(A2, "12/31/2020", "Y") calculates how old someone was on December 31, 2020. Use the format MM/DD/YYYY or the date format your version of Excel expects. You can also put the date in a cell and reference that cell instead of typing it directly.

What if someone's birth date is in the future?

DATEDIF will return a negative number or an error. If you are working with data that includes future dates by mistake, add a check: =IF(A2>TODAY(), "Invalid", DATEDIF(A2, TODAY(), "Y")). This displays "Invalid" for any date in the future and the age for valid birth dates.

Does DATEDIF work the same way in Google Sheets?

DATEDIF exists in Google Sheets but works slightly differently. The syntax is the same, but Google Sheets may require the dates to be in a specific format. If it does not work, use the alternative formula =INT((TODAY()-A2)/365.25), which works in both Excel and Google Sheets.

How do I calculate age if I only have the birth year, not the full date?

If you only have the year in column A, use =YEAR(TODAY())-A2. This subtracts the birth year from the current year. It is less precise than DATEDIF because it does not account for whether the birthday has passed this year, but it works when you do not have the full date.

Can I use this formula to calculate how long ago an event happened?

Yes. DATEDIF works with any two dates, not just birth dates. =DATEDIF(A2, TODAY(), "D") tells you how many days ago an event in cell A2 occurred. Use "Y" for years, "M" for months, or "D" for days, depending on what unit makes sense for your data.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.