How to Calculate Birthday and Age in Excel

Excel has more birthday-calculating power than most people realize. Whether you need someone's current age, days until their next birthday, or how long until a milestone, a handful of date functions handle all of it — once you understand how Excel thinks about dates.

How Excel Stores Dates

Before diving into formulas, it helps to know one thing: Excel stores dates as serial numbers, not the visual format you see on screen. January 1, 1900 = 1. Today might be something like 45,400. This matters because it means dates are just numbers — you can subtract them, compare them, and run math on them like any other value.

That's what makes birthday calculations possible.

Calculating Age from a Birthdate 📅

The most common task: given a birthdate, how old is this person today?

Using DATEDIF (The Hidden Workhorse)

DATEDIF is the most reliable function for this, even though Microsoft doesn't officially document it in modern Excel. It's been there for decades and still works.

Syntax:

=DATEDIF(start_date, end_date, unit) 

To calculate age in completed years:

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

Where A2 contains the birthdate. This returns the number of full years between the birthdate and today — which is exactly how age works.

Unit codes for DATEDIF:

Building a Detailed Age Display

Combine unit codes to show age as years, months, and days:

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

This is useful for HR records, medical forms, or any situation requiring precise age breakdowns.

Using YEARFRAC as an Alternative

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

YEARFRAC calculates the fraction of a year between two dates. Wrapping it in INT() drops the decimal to give you completed years. It's slightly less precise around leap years but works well for most general purposes.

Finding Days Until the Next Birthday

This one's more involved because you need to account for the current year — and the possibility that the birthday has already passed this year.

=IF(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))>=TODAY(), DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))-TODAY(), DATE(YEAR(TODAY())+1,MONTH(A2),DAY(A2))-TODAY()) 

What this does:

• Constructs this year's version of the birthday using DATE, MONTH, and DAY
• Checks whether that date is still in the future
• If yes: subtracts today to get days remaining
• If no: adds one year and recalculates

You can wrap this in a conditional format to highlight upcoming birthdays within 30 days across a list. 🎂

Calculating a Future Birthday (Nth Birthday)

If you want to know what date someone turns 65, 18, or any milestone age:

=DATE(YEAR(A2)+65, MONTH(A2), DAY(A2)) 

Replace 65 with whatever age you're targeting. This returns the exact calendar date of that birthday.

The Leap Year Complication

February 29 birthdays create edge cases in Excel — specifically when you calculate an age or future date in a non-leap year. DATEDIF handles this gracefully by treating Feb 28 as the effective birthday in non-leap years. The DATE function does the same. But it's worth knowing this behavior exists if you're building tools for large datasets with many birthdates — unexpected results on that date are usually traced back here.

Identifying Variables That Affect Your Approach

The right birthday formula depends on several factors:

• What you're displaying — age in years only, a full breakdown, or a countdown — each uses a different formula or combination
• How birthdates are stored — if they're imported as text strings rather than true date values, functions like DATEVALUE() or TEXT-to-Columns conversion may be needed first
• Whether the list is historical or ongoing — a static report uses fixed reference dates; a live HR tracker uses TODAY() so it recalculates automatically
• Your version of Excel — older versions (pre-2016) may behave slightly differently around IFERROR wrapping and certain regional date formats
• Google Sheets vs. Excel — DATEDIF works in both, but some formatting behavior differs

Common Errors and What They Mean

The subtraction method — =(TODAY()-A2)/365.25 — is tempting but produces decimal ages and rounds incorrectly in edge cases. DATEDIF with "Y" is the more accurate choice for anything age-related.

How Complexity Scales With Use Case

A single-cell age calculation is a five-minute task. A birthday tracker for a team of 50, with conditional formatting, automatic alerts, and milestone flags, involves layering multiple functions and possibly a helper column or two. Neither is difficult once you understand the building blocks — but the scope of what you're building determines how many of these formulas you'll need to combine.

The formulas themselves are consistent. What varies is how your data is structured, what you need to display, and how dynamic the spreadsheet needs to be over time.