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

The fastest way to calculate age from a birth date

If you have a birth date in one cell and today's date in another, Excel can calculate exact age in years using the DATEDIF function. Type this formula into an empty cell: =DATEDIF(B2,TODAY(),"Y") where B2 is the cell holding the birth date. Excel will return the number of complete years that have passed.

DATEDIF works because it counts the interval between two dates in the unit you specify — "Y" for years, "M" for months, "D" for days. This is more accurate than dividing the number of days by 365, which ignores leap years and gives a decimal instead of a whole number.

If you want age in months and days as well — say, "28 years, 3 months, 15 days" — you can nest three DATEDIF formulas together in one cell. The formula becomes longer but shows the full breakdown: =DATEDIF(B2,TODAY(),"Y")&" years, "&DATEDIF(B2,TODAY(),"YM")&" months, "&DATEDIF(B2,TODAY(),"MD")&" days". The "&" symbol joins the text and numbers together.

Key Takeaways

DATEDIF calculates the exact number of complete years, months, or days between two dates and handles leap years automatically.
TODAY() returns the current date, so your age calculation updates every time you open the spreadsheet.
You can combine DATEDIF with the ampersand (&) symbol to display age in multiple units — years, months, and days — in a single cell.
If DATEDIF returns an error, check that your birth date is formatted as a date, not text, and that the birth date comes before the comparison date.

Finding days until the next birthday

To count down to someone's next birthday, you need to find the date of this year's birthday and compare it to today. If the birthday has already passed this year, Excel should calculate to next year's birthday instead.

Use this formula: =IF(DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))>=TODAY(),DATE(YEAR(TODAY()),MONTH(B2),DAY(B2)),DATE(YEAR(TODAY())+1,MONTH(B2),DAY(B2)))-TODAY(). Put the birth date in cell B2. The formula checks whether this year's birthday has passed. If it hasn't, it calculates days until this year's birthday. If it has, it calculates days until next year's birthday.

The result will be a number. If you want it to say "42 days until birthday" instead of just "42", wrap the formula in text: =IF(DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))>=TODAY(),DATE(YEAR(TODAY()),MONTH(B2),DAY(B2)),DATE(YEAR(TODAY())+1,MONTH(B2),DAY(B2)))-TODAY()&" days until birthday".

Extracting month and day from a birth date

Sometimes you only need the month and day — for instance, to list birthdays in a calendar without the year. Excel's MONTH and DAY functions pull these out separately.

To get the month as a number (1 through 12), use =MONTH(B2). To get the day as a number (1 through 31), use =DAY(B2). To display them together as "3/15" (March 15th), use =MONTH(B2)&"/"&DAY(B2).

If you want the month name instead of the number — "March" rather than "3" — you need a helper approach. Use =TEXT(B2,"MMMM") to get the full month name, or =TEXT(B2,"MMM") for the three-letter abbreviation. Then combine it with the day: =TEXT(B2,"MMMM")&" "&DAY(B2) produces "March 15".

Handling dates that are entered as text

If your birth dates are stored as text instead of as actual dates, Excel formulas will return errors. You can tell because the dates are left-aligned in their cells instead of right-aligned, and DATEDIF will show #VALUE! error.

To convert text dates to real dates, use the DATEVALUE function: =DATEVALUE(B2). This works if the text is in a standard format like "3/15/1995" or "March 15, 1995". Put the DATEVALUE formula in a new column, then copy the results and paste them back as values into your original column. After that, your age and birthday formulas will work.

If your dates are in an unusual format — like "15-Mar-95" or "1995.03.15" — DATEVALUE may not recognize them. In that case, you may need to use Find & Replace to reformat them first, or manually re-enter a few examples so Excel learns the pattern.

Building a birthday reminder list

Combine age calculation and days-until-birthday into one spreadsheet to track multiple people. Create columns for Name, Birth Date, Current Age, and Days Until Birthday. Then copy your formulas down for each row.

In the Current Age column, use =DATEDIF(B2,TODAY(),"Y") and copy it down. In the Days Until Birthday column, use the countdown formula and copy it down. Sort by the Days Until Birthday column to see whose birthday is coming up soonest.

If you want to highlight birthdays that are within the next week, use conditional formatting. Select the Days Until Birthday column, go to Conditional Formatting, choose "Highlight Cell Rules" > "Less Than", and enter 7. Any birthday within seven days will be highlighted automatically.

Common errors and how to fix them

#VALUE! error usually means your birth date is stored as text, not a date. Check the cell format by right-clicking the cell, selecting Format Cells, and confirming the category is Date, not Text. If it shows Text, use DATEVALUE to convert it.

#NUM! error from DATEDIF means the birth date is after the comparison date — you may have entered the dates in the wrong order, or the birth date is in the future. Swap the cell references so the earlier date comes first.

Negative numbers in your countdown formula mean the birthday has passed and the formula is calculating backward. This happens if you used a less-than comparison instead of greater-than. Check the IF statement and reverse the logic.

Dates showing as numbers like "44987" instead of "3/15/2023" means the cell is formatted as a number instead of a date. Right-click, select Format Cells, choose Date, and pick a format you like.

Frequently Asked Questions

Can I calculate age if I only have the birth year, not the full date?

Yes, but it will be approximate. Use =YEAR(TODAY())-B2 where B2 contains only the year. This gives you age in years, though it will be off by one for part of the year if you do not have the exact month and day. For accurate age, you need the complete birth date.

What if the birth date is in a different time zone?

Excel's TODAY() function uses your computer's local date, so it already accounts for your time zone. If you need to calculate age relative to a different time zone, you would need to manually adjust the date, which is rarely necessary for birthday calculations.

How do I show age in decimal format, like 28.5 years?

Use =YEARFRAC(B2,TODAY()) instead of DATEDIF. YEARFRAC returns the fraction of a year that has passed, so someone who is 28 years and 6 months old shows as 28.5. This is useful for statistical or medical records where partial years matter.

Can I calculate age for someone born on February 29th?

Yes, DATEDIF handles leap year birthdays correctly. On non-leap years, Excel treats February 29 as March 1 for age calculation purposes, so someone born on February 29, 1996 will have their birthday recognized on March 1 in non-leap years. The age calculation remains accurate.

What if I want to calculate age as of a specific date, not today?

Replace TODAY() with a cell reference or a specific date. For example, =DATEDIF(B2,C2,"Y") calculates age as of the date in cell C2. Or use =DATEDIF(B2,DATE(2025,12,31),"Y") to calculate age as of December 31, 2025.

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.