To calculate age from a date of birth in Excel, the most common formula is =DATEDIF(DOB, TODAY(), "Y"), which returns the number of complete years between the two dates. DATEDIF is an undocumented but fully supported Excel function that walks forward from the start date one unit at a time — counting full years first, then leftover months, then remaining days. That same step-by-step counting is what a dedicated online Age Calculator applies internally, using the same approach trusted date libraries such as date-fns use. The catch is that DATEDIF can quietly produce negative day values when a birthday lands on the 31st and the target month is shorter, because Excel does not always borrow the month correctly for every edge case. An online Age Calculator that borrows month-by-month using the actual calendar handles those cases without any debugging. Below you will find the exact Excel formulas, the places they break, and a one-field browser tool that returns the same answer instantly.
Excel stores every date as a serial number — the count of days since January 1, 1900 — so subtracting two cells gives you total days, which handles "how many days have I been alive" perfectly. The moment you want age in years, months, and days, however, the math becomes a calendar problem rather than a subtraction problem, and the rules of plain subtraction no longer apply.
Why Calculating Age from DOB in Excel Gets Tricky
The naive shortcut — subtracting day-from-day and month-from-month — produces impossible values like negative days whenever a birthday lands on the 31st and the target month has only 30 days. Real age arithmetic has to borrow correctly: when the current day is earlier in the month than the birth day, the formula (or the calculator) borrows a full month and adds that month's real length — 28, 29, 30, or 31 — never a fixed 30. Month lengths vary, so the borrowed amount must match the actual calendar.
Leap years add another layer. A year is a leap year under the Gregorian rule if it is divisible by 4, except for century years, which must also be divisible by 400. That means 2000 was a leap year but 1900 and 2100 are not. February has 29 days only in those years, and any age calculation that crosses February has to know whether that February had 28 or 29 days. February 29 birthdays also break naive math, because the calendar day they were born on simply does not exist in three out of every four years. Most age-math bugs come from one of these two sources: wrong month borrowing or wrong February length.
The Excel Formulas for Age from a Date of Birth
Assume your date of birth sits in cell A2 and you want today's age.
- Years only: =DATEDIF(A2, TODAY(), "Y")
- Months leftover after full years: =DATEDIF(A2, TODAY(), "YM")
- Days leftover after full months: =DATEDIF(A2, TODAY(), "MD")
- Combined into one cell: =DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months, " & DATEDIF(A2, TODAY(), "MD") & " days"
- Decimal age in years: =YEARFRAC(A2, TODAY())
- Total whole days alive: =TODAY()-A2
- Age on a future date: =DATEDIF(A2, DATE(2030,6,15), "Y")
The three DATEDIF unit codes "Y", "M", and "D" return complete years, complete months, and complete days between the two dates. The companion codes "YM", "MD", and "YD" return the leftover units after the larger units have been removed, which is exactly the step-by-step counting an age calculator does internally. YEARFRAC is the right tool when a form wants a fractional age, because it returns a decimal like 32.57 instead of a whole-number breakdown.
When DATEDIF returns a #NUM! error, it usually means the end date is earlier than the start date. Wrap the formula in =IF(A2>TODAY(), "Future date", DATEDIF(A2, TODAY(), "Y")) to surface a friendlier message. The same idea lets you lock the end date to a fixed reference — for example, the start of a school year — by replacing TODAY() with DATE(2025, 9, 1).
For a deeper walkthrough of these formulas, the DATEDIF, YEARFRAC, and INT guide covers every variant on one page.
Same Answer, One Click: The Age Calculator
Three DATEDIF calls stacked together give you the right answer, but you have to remember the syntax, fix the negative-day edge case, and rebuild the formula every time the layout changes. The Age Calculator on Lizely does the same arithmetic in one field. You type your date of birth, the years-months-days breakdown appears instantly, and the result updates the moment you change a date — no formulas to type, no edge cases to debug. Everything runs locally in your browser, so your birth date never leaves your device. For HR sheets, school registrations, or insurance forms that ask for age in completed years, you can paste the result back into Excel without rebuilding the formula each time.
The calculator borrows month-by-month using the actual calendar, which is the same approach the date-fns library uses for its differenceInYears, differenceInMonths, and differenceInDays functions. For a hands-on comparison of the Excel approach and the online tool side by side, the age between two dates guide walks through the same problem in both environments.
How to Use the Age Calculator
- Pick your date of birth in the first field — your exact age appears instantly, with no button to press.
- Leave the second date on today, or set it to any past or future date to find your age at that exact moment.
- Read your age in years, months, and days, plus total months, weeks, days, and the countdown to your next birthday.
The calculator uses the same borrow-month-by-month logic as date-fns — stepping forward from your birth date, adding whole years until one more would pass the target date, then whole months, then counting leftover days. That order is what keeps the result non-negative and self-consistent. Total weeks and total days count the same span as the breakdown, just expressed in a single unit, and the live countdown shows the number of days until your next birthday.
Edge Cases That Trip Up Excel Formulas
Three scenarios regularly break Excel formulas and are handled automatically by the Age Calculator.
- February 29 birthdays. February 29 only exists in leap years — years divisible by 4, except century years not divisible by 400, so 1900 and 2100 are not leap years. In common years, the calculator observes the birthday on February 28, the convention used across much of Europe and in many legal systems, so a leap-day baby gains a full year each year rather than only once every four years.
- Month-end birthdays (e.g., January 31). Excel's DATEDIF with the "MD" code can return a negative or wrong day count when the target month is shorter than the birth month. The Age Calculator borrows the actual month length — 28, 29, 30, or 31 — instead of assuming 30.
- Reference date before birth. Excel throws #NUM!; the calculator simply tells you the date is in the future instead of returning a negative age.
None of these edge cases need configuration in the Age Calculator — they are baked into the borrow logic. If you want the same behavior in a workbook, you have to layer IF statements on top of DATEDIF, which is usually more work than the formula is worth for a single check.
Years, Months, Days — and the Totals Alongside Them
Alongside the years-months-days breakdown, the Age Calculator also returns totals: your age expressed as total whole months, total weeks, and total days, plus a live countdown to your next birthday. Total days is the exact calendar-day count between the two dates, using each month's real length and counting every leap day in between. Total weeks is that day count divided by seven and rounded down. Total months is the number of whole months elapsed. These totals count the same span as the years-months-days breakdown, just expressed in a single unit, which is useful for milestones — a baby's age in weeks, an anniversary counted in days, insurance and pension eligibility windows, or the legal age a form asks for.
One cultural note: this Western age count differs from East Asian nominal age (虚岁), where a person is one at birth and gains a year at Lunar New Year rather than on their birthday, so the two systems can differ by one to two years. The Age Calculator returns the Western count, which is what every Excel formula and every official form in the English-speaking world uses.
When to Use Excel vs the Age Calculator
| Scenario | Best choice |
|---|---|
| One-off age check, any reference date | Age Calculator |
| HR sheet with hundreds of DOBs | Excel DATEDIF formula |
| Need a decimal age (YEARFRAC equivalent) | Excel DATEDIF / YEARFRAC |
| Need totals in weeks, days, months at once | Age Calculator |
| Feb 29 birthdays across many rows | Age Calculator |
| Bulk recalculation when the DOB column changes | Excel DATEDIF formula |
| Quick answer on a phone, no spreadsheet open | Age Calculator |
| Age on a fixed reference date (school year start) | Excel DATEDIF with DATE() |
The table describes typical trade-offs rather than a universal rule. For ad-hoc questions, edge cases, and quick checks on any device, the browser tool is faster. For spreadsheet-scale work where the answer has to live in a cell and update as the DOB column changes, the DATEDIF formula stays inside the workbook and keeps the workflow self-contained. The Age Calculator is also a fast sanity check — if a hand-built Excel formula and the calculator disagree, the difference almost always comes down to one of the three edge cases above, and the calculator's borrow-month logic matches the date-fns approach.