The fastest way to calculate age from a DD/MM/YYYY date

To calculate age in Excel from a date in DD/MM/YYYY format, use the DATEDIF function. The formula is =DATEDIF(birth_date, TODAY(), "Y"), where birth_date is the cell containing the date of birth. This returns the person's age in complete years.

If your birth date is in cell A2, the formula becomes =DATEDIF(A2, TODAY(), "Y"). Excel automatically recognizes DD/MM/YYYY dates if your system locale is set to a region that uses that format (such as the UK, Australia, or most of Europe). The result updates every day, so the age increases automatically on each birthday.

DATEDIF is the simplest method because it counts only complete years. Other approaches using YEAR and TODAY require more steps and can give wrong results around birthdays.

Key Takeaways

  • Use =DATEDIF(A2, TODAY(), "Y") to calculate age in years from a DD/MM/YYYY date in cell A2.
  • DATEDIF counts only complete years, so someone born on 15/03/1990 is still 33 years old on 14/03/2024.
  • The formula updates automatically each day, so ages increase on birthdays without manual changes.
  • If DATEDIF returns an error, check that your date is formatted as a date (not text) and that your birth date is before today's date.

Setting up the formula step by step

Start by entering your birth dates in a column. Click on the cell where you want the age to appear — usually the column next to the birth dates. Type the formula exactly: =DATEDIF(A2, TODAY(), "Y"), replacing A2 with the cell reference of your first birth date.

Press Enter. Excel calculates the age and displays it as a number. To apply the same formula to other rows, click the cell containing your formula, then drag the small square at the bottom-right corner of the cell down to the other rows. Excel automatically adjusts the cell references (A2 becomes A3, A4, and so on).

If you see #NUM! or #VALUE! error, the birth date is either formatted as text or is invalid. To fix this, click the cell with the birth date and check the formula bar at the top — if it shows the date in quotes, it is text. Delete it and re-enter the date, then press Enter.

Why DATEDIF works better than other methods

A common alternative is =INT((TODAY()-A2)/365.25), which divides the number of days by 365.25 to estimate years. This method is less accurate because it does not account for leap years properly and can give the wrong age around birthdays. For example, someone born on 29/02/1992 may show the wrong age for several days each year.

Another approach uses =YEAR(TODAY())-YEAR(A2), but this also fails around birthdays. If someone was born on 15/03/1990 and today is 14/03/2024, this formula returns 34 (because it subtracts the birth year from the current year) even though they are still 33. DATEDIF avoids this by counting only complete years from the exact date.

Handling dates when your Excel region is set to MM/DD/YYYY

If your computer is set to a region that uses MM/DD/YYYY format (such as the United States), Excel may misinterpret DD/MM/YYYY dates. For example, 15/03/1990 might be read as an invalid date because there is no 15th month. To prevent this, format your birth dates explicitly as dates before entering the formula.

Click on the cells containing your birth dates, then right-click and select Format Cells. Go to the Number tab, choose Date from the Category list, and select a format that shows DD/MM/YYYY. Click OK. Now re-enter your dates, and Excel will store them correctly. The DATEDIF formula will then work as expected.

Alternatively, if you cannot change your region settings, you can use =DATEDIF(DATE(YEAR(A2),MONTH(A2),DAY(A2)), TODAY(), "Y"), which explicitly tells Excel how to read the date components. This is more complex but works regardless of your system locale.

Calculating age in years, months, and days

If you need more detail than just years, DATEDIF can show months and days as well. Use =DATEDIF(A2, TODAY(), "Y")&" years, "&DATEDIF(A2, TODAY(), "YM")&" months, "&DATEDIF(A2, TODAY(), "MD")&" days". This returns a result like "33 years, 5 months, 12 days".

The three parameters work as follows: "Y" counts complete years, "YM" counts months remaining after complete years are removed, and "MD" counts days remaining after complete months are removed. This formula is longer but useful if you need to show age in a more detailed format.

You can also use just "M" to show total months (=DATEDIF(A2, TODAY(), "M")) or "D" to show total days. Choose whichever unit matches what you are tracking.

Common errors and how to fix them

#NUM! error: This appears when the birth date is after today's date or when the formula is written incorrectly. Check that the birth date is in the past and that you have typed the formula exactly as shown, with commas and quotation marks in the right places.

#VALUE! error: This usually means the birth date is stored as text, not as a date. Click the cell with the birth date and look at the formula bar. If the date appears in quotes or is left-aligned in the cell (instead of right-aligned like numbers), it is text. Delete it and re-enter it without quotes, then press Enter.

Age is one year too high: This happens when you use =YEAR(TODAY())-YEAR(A2) instead of DATEDIF. Switch to the DATEDIF formula to fix it. If you are already using DATEDIF and the age is still wrong, check that the birth date is correct and that today's date is set correctly on your computer.

Frequently Asked Questions

Can I calculate age without using TODAY()?

Yes, but you must enter a specific date instead. Use =DATEDIF(A2, DATE(2024,3,15), "Y") to calculate age as of 15 March 2024. However, this age will not update automatically. Using TODAY() is better because the age updates every day without manual changes.

What if the birth date is in a different column format?

DATEDIF works with any date format as long as Excel recognizes it as a date, not text. If your dates are in YYYY/MM/DD or MM/DD/YYYY format, the formula remains the same. The key is that the cell must be formatted as a date. If you are unsure, right-click the cell, select Format Cells, and choose Date from the Category list.

Can I calculate age for multiple people at once?

Yes. Enter the formula in one cell, then copy it down to all rows with birth dates. Click the cell with the formula, press Ctrl+C (or Cmd+C on Mac), then select the range of cells below it and press Ctrl+V. Excel adjusts the cell references automatically, so each row calculates the age for that row's birth date.

Does the formula work in Google Sheets?

Yes, DATEDIF works the same way in Google Sheets. The formula =DATEDIF(A2, TODAY(), "Y") calculates age identically. Google Sheets also recognizes DD/MM/YYYY dates if your account is set to a region that uses that format.