How to Calculate Age by DOB: Easy Formula, Excel & SQL Methods

Most people who search for how to calculate age by DOB are not just curious about their own birthday math. They are filling out a form that rejects ages under 18, building a spreadsheet for HR, or writing a query that flags customers turning 65 next quarter. The main challenge is usually not knowing your age, but making sure the age calculation is accurate every time using the tool you already have. 

This guide covers all three versions of that problem: getting an instant answer online, the manual formula behind it, and the exact syntax for Excel and SQL, including the specific-date version that most guides skip. Every formula here has been checked against current, working syntax, not copied from another blog.

What Calculate Age by DOB Actually Means

Age looks like simple subtraction: today's year minus birth year. That shortcut is wrong more often than people expect, because it ignores whether the birthday has actually happened yet this year. Someone born on 15 September, checked on 1 March, has not had this year's birthday yet, so straight-year subtraction overstates their age by 1.

Every reliable age calculation, whether it runs in a browser, a spreadsheet, or a database, follows the same underlying rule. This is the concept most competitor pages skip, and it is the one thing worth understanding before you touch a formula.

The Completed-Year Rule (the logic behind every correct age formula):

• Subtract the birth year from the target year to get a rough year count.

• Check whether the birth month and day have occurred yet in the target year.

• If the birthday has not happened yet this year, subtract one more year from the rough count.

Every tool in this guide, from a calculator app to a SQL query, is just that rule written in different syntax. Once you see it this way, the Excel and SQL formulas stop looking like magic and start looking like the same three steps typed differently.

Calculate Age by DOB Online (Fastest Method)

For a one-off answer, typing the date into an online calculator is faster and safer than writing a formula by hand, because a good calculator already applies the Completed-Year Rule correctly, including for 29 February birth dates, which trip up a lot of hand-written formulas.

You can get an instant result with an online age calculator by entering a date of birth and, if needed, a target date other than today. This is the right choice when you need a single answer, not a repeatable formula, and it removes the risk of a manual date-math mistake on something like a legal or medical form.

Use the manual formulas below instead of an online tool when you need the calculation to run automatically across many rows of data, such as a spreadsheet of employees or a database table of customers. That is also where most real mistakes happen, because the formula runs unattended and a small error repeats across every row.

Formula to Calculate Age Based on DOB (Manual Method)

If you are calculating by hand, or explaining the logic to someone else, here is the Completed-Year Rule as a direct formula:

Age = (Target Year − Birth Year) − 1, if (Target Month, Day) is earlier than (Birth Month, Day)
Age = (Target Year − Birth Year), if (Target Month, Day) is on or after (Birth Month, Day)

Worked example: someone born on 10 June 2000, checked on 23 February 2026. The rough count is 2026 minus 2000, which is 26. Because 23 February comes before 10 June, this year's birthday has not happened yet, so the correct age is 25, not 26.

Where people get this wrong by hand:

• Forgetting to check the day, not just the month, when the birth month and target month are the same. Someone born 20 June checked on 10 June has not had the birthday yet, even though both dates fall in June.

• Assuming a 29 February birth date only "has" a birthday in leap years. In practice, most systems treat 28 February or 1 March as the equivalent date in non-leap years, and this needs to be decided deliberately, not left to whatever a formula happens to default to.

How to Calculate Age From Date of Birth to a Specific Date

This is the version most guides leave out, but it is exactly what insurance eligibility checks, school enrollment cutoffs, and contract terms actually need: age as of a date that is not today.

The logic does not change. You simply replace "today" with the target date everywhere in the Completed-Year Rule. This matters because a lot of copy-pasted online formulas hard-code today's date, which quietly breaks the moment someone needs age as of, say, 1 September for a school cutoff or 1 January for a policy renewal.

Practical rule of thumb:

• If your form, spreadsheet, or query only ever needs today's age, hard-coding today is fine and simpler.

• If it needs age as of any other date, even occasionally, build the formula with a target-date variable from the start. Retrofitting a hard-coded formula later is where most of the bugs in this area come from.

If what you actually need is the raw span between two dates rather than an age in years, a dedicated date calculator that counts days, weeks, and months between two dates is often a faster fit than adapting an age formula for the job.

Calculate Age by DOB in Excel

Excel has a function built exactly for this, called DATEDIF. It is real, it works in every current version of Excel, and Microsoft simply does not list it in the formula autocomplete menu, which is why a lot of people never discover it.

The Basic DATEDIF Formula

With a date of birth in cell B2, this returns a whole number of completed years as of today:

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

The "Y" argument tells DATEDIF to count only complete years, which already applies the Completed-Year Rule correctly, including leap-year birth dates. This one formula covers the majority of real spreadsheet use cases.

Age in Years, Months, and Days

For a full breakdown instead of a single number, for example on a certificate or an HR record, combine three DATEDIF calls in one cell:

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

Here, "Y" gives full years, "YM" gives the remaining full months after those years, and "MD" gives the remaining days after those months. Each argument measures a different leftover, not three independent counts, which is the detail people usually get wrong when they try to build this formula from memory.

Age as of a Specific Date in Excel

Swap TODAY() for a fixed date, or a cell reference holding one, and the same function answers age on any date, not just today:

=DATEDIF(B2,DATE(2026,9,1),"Y")

Reference a cell instead of typing the date directly if the target date might change, for example a policy renewal date that gets updated every year. That keeps the formula reusable instead of needing an edit every time the cutoff date moves.

Common Excel Mistakes With Age Formulas

• Typing DATEDIF arguments in the wrong order. The birth date always goes first, the later date second; reversing them returns a #NUM! error.

• Using YEAR(TODAY())-YEAR(B2) as a shortcut. This ignores whether the birthday has passed, and will overstate the age of anyone whose birthday has not happened yet this year.

• Forgetting that DATEDIF only works for dates after 1 January 1900, which occasionally breaks historical or genealogy spreadsheets that store older birth dates as text instead of real dates.

For the full official syntax and additional interval codes, Microsoft documents DATEDIF directly on its Excel function support page, which is worth bookmarking since the function is not listed in Excel's own formula picker.

Calculate Age by DOB in SQL

SQL is where the Completed-Year Rule matters most, because a naive query silently returns the wrong age for every row where the birthday has not yet happened this year, and nobody notices until a report looks off by roughly a year for a chunk of customers.

MySQL

Use TIMESTAMPDIFF, which already applies the Completed-Year Rule correctly:

SELECT TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age FROM customers;

Avoid YEAR(CURDATE()) - YEAR(date_of_birth) as a shortcut. It looks correct and passes casual testing, but it overstates age by one for every customer whose birthday has not happened yet in the current year, which is roughly a third of any customer base at any given time.

SQL Server

This is the database where people get burned most often, because DATEDIFF on its own does not apply the Completed-Year Rule. It counts how many calendar-year boundaries were crossed, not full elapsed years, so it overstates age for anyone whose birthday has not happened yet:

SELECT DATEDIFF(YEAR, DateOfBirth, GETDATE())
  - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, DateOfBirth, GETDATE()), DateOfBirth) > GETDATE()
        THEN 1 ELSE 0 END AS Age
FROM Customers;

The CASE statement is doing the actual Completed-Year Rule check: it rebuilds this year's birthday from the raw DATEDIFF count, and if that reconstructed birthday is still in the future, it subtracts the extra year. Skipping this CASE block is the single most common SQL Server age bug in production systems.

PostgreSQL

PostgreSQL has a purpose-built AGE function that returns a full interval, not just a year count, and it applies the Completed-Year Rule internally:

SELECT EXTRACT(YEAR FROM AGE(CURRENT_DATE, date_of_birth)) AS age FROM customers;

Because AGE() returns years, months, and days as an interval, you can pull out any part of it, not just the year, which makes PostgreSQL the least error-prone of the three for this specific task.

Age as of a Specific Date in SQL

Every query above accepts a fixed date in place of CURDATE(), GETDATE(), or CURRENT_DATE. This is the SQL version of the same rule as Excel: hard-code the current date only if you are certain you will never need age as of any other date.

-- MySQL, age as of a specific date
SELECT TIMESTAMPDIFF(YEAR, date_of_birth, '2026-09-01') AS age FROM customers;
-- PostgreSQL, age as of a specific date
SELECT EXTRACT(YEAR FROM AGE(DATE '2026-09-01', date_of_birth)) AS age FROM customers;

For the complete function reference beyond age calculations, see the official MySQL date and time function documentation and the PostgreSQL date and time function documentation, both of which stay current with the latest supported syntax.

Real-World Scenarios Where Age Calculation Mistakes Actually Cost Something

These are not hypothetical edge cases. Each one is a place where the Completed-Year Rule gets skipped in practice, and the mistake is usually invisible until someone downstream notices the number is off.

• Insurance and eligibility systems: a naive SQL Server DATEDIFF query without the CASE-statement fix can flag a customer as eligible for a senior discount or a policy tier almost a year before they actually qualify.

• School enrollment cutoffs: districts often need age as of a fixed date, such as 1 September, not the application date. A spreadsheet built with TODAY() instead of a fixed cutoff date silently drifts wrong as the school year progresses.

• HR and payroll systems: a YEAR()-YEAR() shortcut in a spreadsheet can misreport who is turning a benefits-eligible age this quarter, which either triggers incorrect enrollment or misses people who should have been enrolled.

• Data migrations: when age is calculated once and stored as a static number instead of derived from date of birth, every record becomes wrong exactly one day after it was calculated, and nobody notices until an audit.

Common Mistakes People Make Calculating Age

• Storing age as a fixed value instead of storing date of birth and calculating age on demand. Age changes every year; date of birth does not.

• Reversing the start and end date order in DATEDIF or TIMESTAMPDIFF, which either errors out or silently returns a negative number.

• Using plain DATEDIFF in SQL Server without the birthday-check CASE statement, which is the most common source of off-by-one age bugs in reporting queries.

• Not deciding, in advance, how a 29 February birth date should behave in non-leap years, then getting inconsistent results across different systems that each default differently.

• Copy-pasting a formula from a forum answer without checking whether it hard-codes today's date, then being confused later when the business asks for age as of a different date.

Quick Reference: Formula by Tool

Conclusion

Calculating age by date of birth may look simple, but getting the exact result requires more than subtracting one year from another. You also need to check whether the birthday has already occurred in the target year. This Completed-Year Rule is the basic logic behind accurate age calculations.

For a quick answer, an online age calculator is usually the easiest option. If you need to calculate age for many records, Excel and SQL formulas can save time and reduce repeated manual work. You can also calculate age as of a specific date by replacing today's date with your required target date.

The key is simple: check the birth year, check whether the birthday has passed, and then adjust the result if needed. Once you understand this rule, calculating age accurately becomes much easier across online tools, spreadsheets, and databases.

Frequently Asked Questions

Q: How do I calculate age by DOB online?

Enter the date of birth and a target date if it is not today into an online age calculator. It applies the Completed-Year Rule automatically, including for leap-year birth dates.

Q: What is the formula to calculate age based on DOB?

Subtract the birth year from the target year, then subtract one more if the birth month and day have not yet occurred in the target year. Every spreadsheet and database formula in this guide is this same rule written in different syntax.

Q: How do I calculate age from date of birth to a specific date, not today?

Replace today's date with the target date everywhere the formula would normally use it: TODAY() in Excel, or a fixed date in place of CURDATE(), GETDATE(), or CURRENT_DATE in SQL. The underlying calculation does not change.

Q: What is the formula to calculate age based on DOB in Excel?

=DATEDIF(B2, TODAY(), "Y") for a whole number of years, where B2 holds the date of birth. Add "YM" and "MD" arguments if you also need the remaining months and days.

Q: How do I calculate age by DOB in SQL?

Use TIMESTAMPDIFF(YEAR, dob, CURDATE()) in MySQL, EXTRACT(YEAR FROM AGE(CURRENT_DATE, dob)) in PostgreSQL, or DATEDIFF(YEAR, dob, GETDATE()) paired with a CASE statement that checks whether the birthday has occurred yet in SQL Server.