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

Calculating age in Excel sounds straightforward — subtract a birth date from today. But if your dates are formatted as DD/MM/YYYY, you've probably already discovered that Excel doesn't always cooperate. Regional date settings, formula logic, and how Excel interprets date values all interact in ways that trip people up. Here's how the whole thing actually works.

Why DD/MM/YYYY Creates a Specific Challenge

Excel stores dates as serial numbers internally — January 1, 1900 is 1, January 2 is 2, and so on. The display format (DD/MM/YYYY, MM/DD/YYYY, etc.) is a visual layer on top of that number.

The problem arises when Excel misreads date input. If your system locale is set to US English, Excel expects MM/DD/YYYY. Type 15/06/1990 and Excel may treat it as text, not a date — which breaks every formula you apply to it.

So before any age calculation works reliably, you need to confirm two things:

• Are your dates stored as actual date values or as text strings?
• Is your Excel locale aligned with the DD/MM/YYYY format you're entering?

You can check quickly: click a date cell and look at the formula bar. If it shows 15/06/1990 exactly as typed, it might be text. If it shows 6/15/1990 or a date-formatted value, Excel has recognised it as a real date.

The Core Formula: DATEDIF

The most reliable function for calculating age in completed years is DATEDIF — a legacy function that Excel supports but doesn't list in its autocomplete. It calculates the difference between two dates in a specified unit.

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

• birth_date — the cell containing the date of birth
• TODAY() — returns the current date automatically
• "Y" — returns the number of complete years elapsed

Example: If A2 contains a valid date for 15 June 1990, the formula =DATEDIF(A2, TODAY(), "Y") returns the person's age in full years as of today.

This formula works regardless of how the date is displayed, as long as the underlying value is a real Excel date. The DD/MM/YYYY format is purely cosmetic at the formula level.

📅 Formatting Date Cells Correctly

If you want dates displayed in DD/MM/YYYY format, you format the cells — you don't change the data type.

1. Select your date cells
2. Press Ctrl + 1 to open Format Cells
3. Go to Number → Date
4. Choose a DD/MM/YYYY format from the list, or enter a custom format: dd/mm/yyyy

This tells Excel how to show the value without changing what the value is. Your age formulas continue working on the underlying serial number.

What If Your Dates Are Stored as Text? 🔧

This is where many spreadsheets break. Common signs your dates are text:

• Left-aligned in the cell (Excel right-aligns real dates by default)
• A small green triangle in the cell corner
• Age formulas return errors like #VALUE!

Fix option 1 — DATEVALUE:

If your text dates follow a consistent DD/MM/YYYY pattern, you can convert them:

=DATEVALUE(TEXT(A2,"DD/MM/YYYY")) 

Or more directly, if the cell contains 15/06/1990 as text:

=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)) 

This manually extracts the day, month, and year from the text string and assembles a proper date value. Once converted, apply your DATEDIF formula on the result.

Fix option 2 — Text to Columns:

Select the column, go to Data → Text to Columns, click through to Step 3, set the column data format to Date: DMY, and finish. Excel re-interprets the text as proper dates.

Calculating Age with More Detail

If you need more than just years — say, years, months, and days — DATEDIF handles that too with different unit codes:

To display age as "34 years, 5 months, 12 days":

=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days" 

This concatenates three separate DATEDIF calls into a readable text string.

Alternative: The YEARFRAC Approach

Another method uses YEARFRAC, which calculates the fraction of a year between two dates:

=INT(YEARFRAC(A2, TODAY())) 

INT rounds down to the nearest whole number, giving you the completed age. This approach behaves slightly differently from DATEDIF at edge cases (like birthdays falling on leap days), so results can occasionally differ by one day — worth knowing if precision matters for your use case.

Variables That Affect How This Works for You

Several factors shape which approach will actually work in your spreadsheet:

• Your system locale — Excel on a UK or Australian system will natively accept DD/MM/YYYY input; US locale systems often won't
• How the data entered the spreadsheet — imported CSVs, copied text, or manually typed entries all behave differently
• Excel version — DATEDIF behaves consistently across versions, but some newer functions (like DAYS or array-based approaches) vary between Excel 2016, 2019, Microsoft 365, and Excel for Mac
• Whether you're using Excel Online — the web version has some formatting limitations compared to the desktop application
• Data scale — a column of 20 dates and a database of 20,000 both use the same formulas, but text-conversion approaches need to be consistently applied across every row

A spreadsheet where dates were typed manually on a UK-locale machine will behave very differently from one where dates were imported from a US-formatted CSV — even if every cell looks identical on screen.

The right formula path depends entirely on how your data got into Excel and what your system is set up to expect.