Calculate Age in Excel From DD/MM/YYYY With a Formula

To calculate someone’s age in Excel from a DD/MM/YYYY birth date, enter the birth date as a real Excel date, then use =DATEDIF(A2,TODAY(),"Y") in the age column, assuming the birth date is in A2. The formula returns completed years and updates as the current date changes. First make sure Excel has interpreted day and month in the correct order; a date stored as text or read as MM/DD/YYYY can give an incorrect result.

What You’ll Need

Microsoft Excel for Microsoft 365 on Windows, or Excel for Mac or the web. The core formula works across these versions.
A birth date in a worksheet cell, such as A2, and an empty cell for the age result.
The birth date needs to be an Excel date value, not just text that looks like a date. The displayed format can be DD/MM/YYYY, but the underlying value must be recognized correctly.

Step-by-Step

Set up a clear layout. Put a heading such as Birth date in A1 and Age in B1. Enter each person’s date of birth in column A, starting at A2, and leave the corresponding cells in column B empty. For example, enter 14/08/1990 in A2 if that person was born on 14 August 1990. If you already have a list, identify the columns that hold birth dates and age results before adding the formula, so you do not overwrite existing information.
Check that Excel reads the date as day/month/year. Click the birth-date cell and look at the formula bar and the displayed value. A date can display with slashes and still be text, so changing its number format alone will not necessarily convert it. In a region that uses day/month/year, Excel will often recognize a typed date automatically. If your computer uses a different regional date order, a value such as 05/06/1990 may be ambiguous: it could mean 5 June or May 6. Confirm the intended date before calculating ages.
Convert imported text dates if needed. Make a copy of the original date column first, particularly if the workbook contains important data. Select the copied dates, then choose Data > Text to Columns. In the wizard, select Delimited and click Next, click Next again, choose Date and select DMY from the date-order list, then click Finish. Excel should convert strings such as 14/08/1990 into real dates using day, month, year order. Verify a few converted cells before using them, especially if the source data includes multiple date formats or missing values.
Enter the age formula. With birth dates in A2:A20 and the age result for the first person in B2, click B2 and enter =DATEDIF(A2,TODAY(),"Y"). Press Enter. DATEDIF calculates the difference between the start date and end date; the “Y” option asks for the number of complete years. For a birth date of 14/08/1990 and today’s date of 02/11/2026, the result is 36: the person has had their 36th birthday, but not their 37th.
Fill the formula down the list. Select B2 and point to the small square at the bottom-right corner of the selection, called the fill handle. Drag it down to the last row of birth dates. Excel adjusts the reference for each row, so the next formula uses A3, then A4, and so on. You can also copy B2, select the remaining result cells, and paste. On Windows, Ctrl+C and Ctrl+V copy and paste; on Mac, use Command+C and Command+V. Check that the first and last formulas refer to the date on the same row as each result.
Choose how the results should appear. Completed ages are whole numbers, so Excel’s default General format is suitable. If the cells show dates or an unexpected display, select the age cells and choose Home > Number Format, then select General or Number. Avoid using a date format for the result column. If you want a label such as “36 years,” use a separate display column or a custom number format; keeping the age as a number makes it easier to sort, filter, and use in later calculations.
Use a fixed date instead of today when required. For a report that must calculate age as of a particular date, put the chosen reporting date in B1 and use a separate result column. With the birth date in A2 and the as-of date in B1, enter =DATEDIF(A2,$B$1,"Y") in C2. The dollar signs keep the reference to B1 fixed when you fill the formula down. For example, if A2 is 20/12/1990 and B1 contains 02/11/2026, the result is 35 because the person’s 36th birthday has not yet occurred. Make sure B1 is a real date too.
Save and review a few results. Check at least one person whose birthday has already occurred this year and one whose birthday is still coming up. Confirm that each result is the number of birthdays completed by the current or selected as-of date, rather than just the difference between the calendar years. Save the workbook when the results look correct.

Common Mistakes

Using year subtraction alone. Subtracting the birth year from the current year overstates age for anyone whose birthday has not happened yet this year. DATEDIF with “Y” accounts for whether the birthday has passed.
Mixing up DD/MM/YYYY and MM/DD/YYYY. Excel may interpret a typed or imported date using your computer’s regional settings. A date such as 04/07/1995 can silently become April 7 instead of 4 July. Check ambiguous dates against the source before calculating.
Trying to calculate from text. If a date is stored as text, Excel may return an error or fail to use it as a date. Applying a date display format to text does not necessarily convert it; use Text to Columns with DMY selected, then inspect the result.
Including the wrong cell in the formula. DATEDIF needs the birth date first and the later calculation date second. Reversing them, or pointing to a heading or blank cell, can produce an error or an implausible age.
Expecting a decimal age. The “Y” unit returns completed years only. It does not return months or a fraction of a year. A result of 35 means 35 full years have elapsed, not that the person is exactly 35 years old in every unit of time.

Tips

Format the birth-date column as DD/MM/YYYY so dates are easier to read. On Windows, select the cells and press Ctrl+1 to open Format Cells; on Mac, select the cells and press Command+1. Choose a date format with day first. Formatting changes how a valid date appears, not the date value itself.
If you convert several imported dates, retain an untouched copy of the source column until you have checked the conversion. This makes it easier to correct a mistaken DMY choice without reimporting the data.
Keep the as-of date in one cell for a dated report. Referencing that cell means the whole list uses the same calculation date and lets you update the report consistently. By contrast, TODAY() is useful for an age list intended to stay current.
When you turn the list into an Excel table, a formula entered in the age column usually fills down automatically for existing and new rows. Confirm the formula references the date column in the same row before relying on the table.
For a clean report, add a label such as Age (years) and use whole-number results. This makes clear that the figure is completed years, rather than years and months.

Troubleshooting

The formula returns #VALUE!. The birth date or the as-of date may be text rather than a date value. Try converting the source using Data > Text to Columns and the DMY setting. If only some rows fail, check those specific cells for leading spaces, apostrophes, or a different date pattern.

The formula returns #NUM!. DATEDIF returns this error when the start date is later than the end date. Check that the birth date is not in the future and that the date arguments have not been reversed. Also check whether a text date was interpreted with the wrong day and month order.

The age seems one year too high or too low. Compare the birth month and day with today’s date, or with the reporting date in your chosen as-of cell. If the birthday is still ahead, the age should be one less than the simple current-year-minus-birth-year calculation. If it is not, confirm that Excel has not swapped the day and month.

The formula appears in the cell instead of a number. The result cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell format to General, then enter the formula again. A leading apostrophe tells Excel to display the entry as text rather than calculate it.

The age does not change tomorrow. TODAY() updates when Excel recalculates the workbook; it is not a continuously running clock. Press F9 on Windows to recalculate formulas if needed. On Mac, use Fn+F9 on keyboards where function keys control hardware features, or use the Formulas tab’s calculation commands. If calculation is set to Manual, change it to Automatic through Formulas > Calculation Options > Automatic. Excel for the web generally recalculates when the workbook changes or is opened, but a deliberate refresh or edit may be needed if a value appears stale.

Frequently Asked Questions

Does the formula work with DD/MM/YYYY dates?
Yes, as long as Excel has stored the value as a date and interpreted day, month, and year correctly. DATEDIF uses Excel’s underlying date value, not the way the date is displayed. Set the date column to a day-first format for easier checking.

Will the age update automatically?
Yes. TODAY() returns the current date, so the calculated age changes after the person’s birthday once the workbook recalculates. A fixed date entered in a cell is better when you need a reproducible report for a past or future date.

Can I calculate age in years and months?
Yes. With a birth date in A2 and the calculation date set to today, use =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months". It reports completed years and the remaining completed months; for 14/08/1990 on 02/11/2026, that is 36 years, 2 months. If you use an as-of date in a cell, replace each TODAY() with that cell reference.

Why does Excel not show DATEDIF in the function list?
DATEDIF is an older compatibility function and may not appear in Excel’s autocomplete or function list. It is still usable: type the formula directly, include the quotation marks around the unit, and check that the cell references point to valid dates.

Can I use this in Excel for Mac or Excel for the web?
Yes. The DATEDIF and TODAY formula works in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Ribbon locations and keyboard shortcuts can differ, but the formula syntax is the same.

What if I want age as of a date in another cell?
Put that calculation date in a cell and use it as DATEDIF’s second argument, such as =DATEDIF(A2,$B$1,"Y") when the birth date is in A2 and the as-of date is in B1. This gives every row a consistent reference date and avoids the automatic change caused by TODAY().

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.