How to Calculate Someone's Age or Days Until Their Birthday in Excel

The fastest way to calculate age from a birthdate

Use the DATEDIF function to find someone's exact age in years. If a birthdate is in cell A2, type =DATEDIF(A2,TODAY(),"Y") in another cell and press Enter. Excel will show their age as a whole number.

DATEDIF compares two dates and returns the difference in the unit you specify. The "Y" at the end means years. You can also use "M" for months or "D" for days if you need a more precise measurement.

This method updates automatically every day, so the age will increment on the person's birthday without you having to change anything.

Key Takeaways

DATEDIF is the simplest function for age: type =DATEDIF(birthdate,TODAY(),"Y") to get years, or use "M" for months and "D" for days.
If DATEDIF does not work, use =INT((TODAY()-A2)/365.25) as an alternative that works in all Excel versions.
To find how many days until the next birthday, use =DATEDIF(TODAY(),DATE(YEAR(TODAY())+1,MONTH(A2),DAY(A2)),"D").
Birthdates must be formatted as dates, not text — if your formula returns an error, right-click the cell and choose Format Cells > Date.

Alternative formula if DATEDIF does not work

Some older Excel versions or regional settings do not recognize DATEDIF. If you get a #NAME? error, use this instead: =INT((TODAY()-A2)/365.25). This divides the number of days between today and the birthdate by 365.25 (accounting for leap years) and rounds down to a whole number.

The INT function removes the decimal portion, so 45.8 years becomes 45. This formula is less precise than DATEDIF for exact month and day calculations, but it works reliably across all Excel versions and platforms.

Calculating days until the next birthday

To show how many days remain until someone's next birthday, use: =DATEDIF(TODAY(),DATE(YEAR(TODAY())+1,MONTH(A2),DAY(A2)),"D"). Replace A2 with the cell containing the birthdate.

This formula constructs the next occurrence of the birthday (using the month and day from the birthdate, but the year set to next year) and counts the days from today until that date. On the birthday itself, the result will be 365 or 364 (depending on leap years).

Formatting birthdates so Excel recognizes them

Excel treats dates as numbers, so a birthdate must be in a recognized date format. If you type "1985-03-15" or "3/15/1985", Excel usually converts it automatically. If you paste dates from another source, they might arrive as text instead, and formulas will not work.

To check: click the cell with the birthdate. If it is text, it will be left-aligned in the cell. If it is a date, it will be right-aligned. To convert text to a date, select the column, go to the Data tab, click Text to Columns, choose Delimited, click Next twice, set the Column Data Format to Date, and click Finish.

Alternatively, right-click the cell, choose Format Cells, select the Date category, pick a format, and click OK. This does not convert text to a date, but it ensures any new dates you enter will be recognized correctly.

Calculating age in years and months

If you need age broken down as "45 years, 7 months", use DATEDIF twice in separate cells or combine them with text. In one cell, type =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months". The "YM" parameter returns only the months beyond the last full year.

The ampersand (&) joins the results together with text labels. The output will read something like "45 years, 7 months" in a single cell. If you want just the numbers in separate columns, put =DATEDIF(A2,TODAY(),"Y") in one cell and =DATEDIF(A2,TODAY(),"YM") in the next.

Highlighting upcoming birthdays in a list

If you have a spreadsheet with many birthdates, you can highlight rows where the birthday falls within the next 30 days. Select the range of cells containing birthdates, go to the Home tab, click Conditional Formatting, choose New Rule, and select "Use a formula to determine which cells to format".

In the formula box, type: =AND(MONTH(A2)=MONTH(TODAY()),DAY(A2)>=DAY(TODAY())) (replace A2 with your first cell). This highlights birthdays occurring this month from today onward. Choose a fill color and click OK. For a wider window, use =DATEDIF(TODAY(),A2,"D")<=30 to highlight any birthday within 30 days.

Common errors and how to fix them

A #NUM! error usually means the birthdate is after today's date — check that the year is correct. A #VALUE! error means one of the cells contains text instead of a date. A #NAME? error means Excel does not recognize the function name, which happens with DATEDIF in some regional settings — switch to the INT formula instead.

If a formula returns a negative number or an unreasonably large number, the birthdate cell is probably formatted as text. Click the cell, check the formula bar at the top to see exactly what is stored there, and reformat or re-enter the date. You can also try wrapping the birthdate reference in DATEVALUE: =DATEDIF(DATEVALUE(A2),TODAY(),"Y").

Frequently Asked Questions

What is the difference between DATEDIF and the INT formula?

DATEDIF is built specifically for date differences and works in modern Excel versions. INT is a workaround that divides days by 365.25 and rounds down. DATEDIF is more accurate for month and day calculations, but INT works in older versions and some regional settings where DATEDIF is not available.

Can I calculate age if I only have the birth month and year, not the day?

Yes, but the result will be approximate. If the day is missing, enter the 15th as a placeholder (for example, 1985-03-15). The age will be off by up to half a year depending on where in the month the actual birthday falls. For precise age, you need the full date.

How do I make the age update automatically without opening the file?

Excel recalculates formulas when you open the file, not while it is closed. The TODAY() function will update to the current date each time you open the spreadsheet, so ages will be correct. If you need real-time updates while the file is open, you would need a macro, which is beyond basic Excel formulas.

What if the birthdate is in a different column format, like "March 15, 1985"?

Excel recognizes most common date formats automatically. If it does not, the cell will show the text as written and formulas will fail. Right-click the cell, choose Format Cells, select Date, pick a format, and click OK. If the date still does not work, it is stored as text — use the Text to Columns method described in the formatting section above.

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.