How to Calculate Age in Excel Using DD/MM/YYYY Dates

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

To calculate someone's age in Excel from a date in DD/MM/YYYY format, use the DATEDIF function combined with TODAY(). The formula is:

=DATEDIF(B2,TODAY(),"Y")

Replace B2 with the cell containing the birth date. This returns the person's age in complete years. The "Y" at the end tells Excel to count only full years, ignoring months and days. If your dates are stored as text rather than actual dates, you may need to convert them first — Excel will show an error if it does not recognise the cell as a date.

This method works whether your dates are formatted as DD/MM/YYYY, MM/DD/YYYY, or any other format, as long as Excel recognises them as dates internally. The display format does not matter; what matters is whether the cell contains an actual date value or just text that looks like a date.

Key Takeaways

DATEDIF(birthdate, TODAY(), "Y") calculates age in complete years and is the simplest method for most spreadsheets.
If your dates are stored as text, convert them to actual dates first using the DATE function or by reformatting the cells.
You can calculate age in months or days by changing the third parameter from "Y" to "M" or "D".
The TODAY() function updates automatically each day, so ages recalculate without manual updates.

Converting text dates to actual dates before calculating

If your DD/MM/YYYY dates are stored as text (which happens when data comes from certain databases or imports), DATEDIF will not work. You can tell because the formula returns an error or the dates are left-aligned in their cells instead of right-aligned.

To convert text to a date, use the DATEVALUE function inside DATEDIF:

=DATEDIF(DATEVALUE(B2),TODAY(),"Y")

This tells Excel to treat the text in B2 as a date first, then calculate the age. If you have many rows of text dates, convert the entire column at once: create a helper column with =DATEVALUE(B2), copy it down, then copy and paste the results as values back into the original column. After that, you can use the straightforward DATEDIF formula without DATEVALUE.

Calculating age in months or days instead of years

The third parameter in DATEDIF controls what unit Excel counts. Use "Y" for years, "M" for months, or "D" for days. You can also combine them to show age as "years and months".

For age in months only:

=DATEDIF(B2,TODAY(),"M")

For age in days only:

=DATEDIF(B2,TODAY(),"D")

To show age as "25 years, 3 months", use two DATEDIF formulas in one cell:

=DATEDIF(B2,TODAY(),"Y")&" years, "&DATEDIF(B2,TODAY(),"YM")&" months"

The "YM" parameter counts only the months beyond the last full year, so it never returns a number larger than 11. This format is useful for medical records, employee files, or anywhere you need precision beyond just the year.

What to do if DATEDIF returns an error

DATEDIF fails for three common reasons: the birth date is after today's date, the cells contain text instead of dates, or the date format is not recognised.

If the birth date is in the future, you will see a #NUM! error. Check that you have the dates in the right order — the start date (birth date) must come before the end date (today). If someone's birth date is genuinely in the future, you may want to show age as 0 or use a different calculation.

If you see a #VALUE! error, the cells likely contain text. Use DATEVALUE as shown above, or check that the cells are formatted as Date, not Text. Right-click the cell, choose Format Cells, and select Date from the Category list.

If the date format is ambiguous — for example, 03/04/2000 could mean March 4th or April 3rd — Excel may misinterpret it. Make sure your dates are unambiguous (use a format like 03-Apr-2000) or enter them in a way Excel recognises, such as through the date picker.

Using YEARFRAC for fractional age

If you need age as a decimal (for example, 25.7 years instead of 25 years), use YEARFRAC instead of DATEDIF:

=YEARFRAC(B2,TODAY())

This calculates the exact fraction of a year between the birth date and today. It is useful for scientific studies, insurance calculations, or anywhere you need precision to one decimal place. YEARFRAC also handles text dates better than DATEDIF in some versions of Excel, though DATEVALUE is still the safer approach.

YEARFRAC returns a number like 25.342, which you can round to one decimal place with =ROUND(YEARFRAC(B2,TODAY()),1) if you want a cleaner display.

Calculating age on a specific date instead of today

If you need to calculate how old someone was on a past date — for a historical record, a report from last year, or a specific event — replace TODAY() with that date:

=DATEDIF(B2,DATE(2023,12,25),"Y")

This calculates age as of December 25, 2023. You can also reference a cell containing a date:

=DATEDIF(B2,C2,"Y")

This is useful when you have a column of event dates and want to know how old each person was at that event. Copy the formula down the column and it recalculates for each row automatically.

Avoiding common mistakes with age calculations

The most common mistake is subtracting birth year from current year with a straightforward formula like =(YEAR(TODAY())-YEAR(B2)). This gives the wrong answer for anyone whose birthday has not yet occurred this year. DATEDIF avoids this by counting actual days between dates.

Another mistake is forgetting that DATEDIF requires dates in the correct order. The start date must come before the end date, or you get an error. If you are calculating age from a future date by accident, swap the order of the parameters.

A third mistake is mixing text and actual dates in the same column. If some cells contain dates and others contain text that looks like dates, some formulas will work and others will fail. Clean your data first by converting all text dates to actual dates, or use DATEVALUE to handle both types at once.

Frequently Asked Questions

Why does my DATEDIF formula show #NUM! error?

This usually means the birth date is after today's date, or the dates are in the wrong order. Check that the first date (birth date) comes before the second date (today or your reference date). If the birth date is genuinely in the future, the formula cannot calculate a positive age.

Can I use DATEDIF if my dates are in MM/DD/YYYY format instead of DD/MM/YYYY?

Yes. DATEDIF works with any date format as long as Excel recognises the cell as a date, not text. The display format does not matter — only whether the cell contains an actual date value. If you are unsure, format the cell as Date and re-enter the value.

How do I calculate age for multiple people at once?

Enter the formula in the first row (for example, =DATEDIF(B2,TODAY(),"Y") in cell C2), then copy it down to all other rows. Excel automatically adjusts the cell reference for each row, so B2 becomes B3, B4, and so on. Select the cell with the formula, copy it, select the range where you want it, and paste.

What is the difference between DATEDIF and YEARFRAC?

DATEDIF returns whole years, months, or days. YEARFRAC returns a decimal (for example, 25.342 years). Use DATEDIF for straightforward age in years; use YEARFRAC when you need fractional precision for scientific or insurance calculations.

Can I calculate age without using TODAY(), so the ages do not change every day?

Yes. Replace TODAY() with a specific date, like =DATEDIF(B2,DATE(2024,1,1),"Y"). This calculates age as of that fixed date and does not update automatically. This is useful for historical records or reports where you want ages to stay the same.

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.