Calculate Age in Excel Using an ID Number with a Birth Date

If an ID number begins with a date of birth, you can extract its year, month, and day into helper columns, turn them into an Excel date, then use DATEDIF to return the person’s age in completed years. This works only when you know the ID’s date format and century rule; ID numbers do not follow one universal pattern.

What You’ll Need

An ID number in a worksheet cell, with its date-of-birth digits at a known position. The example below assumes the first six characters are YYMMDD.
The rule for interpreting the two-digit year. The example assumes 00–26 means 2000–2026 and 27–99 means 1927–1999, based on the current year in 2026.
Excel for Microsoft 365 on Windows. The formulas also work in current Excel for Mac and Excel for the web, though some import and menu details may differ.

Step-by-Step

Confirm what the ID encodes. Check the issuing organization’s documentation before building a formula. Some IDs contain a date in YYMMDD order; others use DDMMYY, include a century indicator, encode no birth date at all, or use characters that only resemble a date. Do not assume that the first six digits are a birth date just because they can be split into plausible numbers. If the ID has a separate birth-date field in the source data, use that instead. The sample here uses the fictional ID 9002151234, whose first six characters represent 15 February 1990.
Keep the ID as text and label your columns. Put the ID in A2 and use row 1 for headings: ID in A1, YY in B1, MM in C1, DD in D1, Birth date in E1, and Age in F1. Set column A to Text before entering or importing IDs if possible, especially when IDs can start with zero. On Windows, use Home > Number Format > Text, or press Ctrl+1 and choose Text in Format Cells; on Mac, Command+1 opens Format Cells. If importing a CSV in Microsoft 365, use Data > From Text/CSV and set the ID column’s data type to Text in the import or Power Query steps. Changing a number to Text after Excel has removed a leading zero cannot restore it.
Extract the two-digit year. With the ID in A2 and the YY helper column in B, enter this formula in B2. For 9002151234, the result is 90. It reads the first two characters, so keeping A2 as text also protects an ID beginning with zero.
With the ID in A2, enter in B2: =VALUE(LEFT(A2,2))
Extract the month. In C2, read two characters starting at position 3. For the sample ID, the result is 2, meaning February. Excel can use a single-digit month in a date formula, so a result of 2 is fine; you can display it with a leading zero later if you prefer.
With the ID in A2, enter in C2: =VALUE(MID(A2,3,2))
Extract the day. In D2, read two characters starting at position 5. The sample returns 15. Check that these positions match the documented ID pattern. If the date appears later in the ID, change the starting positions; for example, an ID whose date begins at character 4 needs different positions. Do not simply shift the formula until the result looks reasonable: verify it against known records.
With the ID in A2, enter in D2: =VALUE(MID(A2,5,2))
Build a full birth date. In E2, convert the two-digit year into a four-digit year and pass the year, month, and day to DATE. The example’s 90, 2, and 15 become 15 February 1990. The century rule in this formula is a rolling rule: in 2026, years 00–26 are treated as 2000–2026, while 27–99 are treated as 1927–1999. Change this logic if the issuer has a different rule or your records include people outside that assumed range.
With YY in B2, MM in C2, and DD in D2, enter in E2: =DATE(IF(B2<=MOD(YEAR(TODAY()),100),2000,1900)+B2,C2,D2)

Format E as a date so it is readable. Select E2, then choose Home > Number Format and select Short Date, or press Ctrl+1 on Windows (Command+1 on Mac) and select a date format. The underlying value remains an Excel date serial, which is what the age calculation needs.
Calculate age in completed years and flag bad dates. Put the following formula in F2. It checks that Excel did not roll an invalid date into another month (for example, February 30), rejects a birth date in the future, and otherwise returns completed years as of today. With the sample ID, the birth date is 15 February 1990, so on 2 November 2026 the result is 36. TODAY updates automatically as the workbook recalculates. IFERROR displays “Check ID” if the ID is too short or one of the helper formulas returns an error.
With B2:D2 holding the extracted parts and E2 holding the constructed date, enter in F2: =IFERROR(IF(OR(YEAR(E2)<>IF(B2<=MOD(YEAR(TODAY()),100),2000,1900)+B2,MONTH(E2)<>C2,DAY(E2)<>D2,E2>TODAY()),"Check ID",DATEDIF(E2,TODAY(),"Y")),"Check ID")
Fill the formulas down. Select B2:F2 and drag the small square at the lower-right corner of the selection down to the last record. Excel adjusts the row references for each ID. Alternatively, select the range to fill and press Ctrl+D on Windows; in Excel for Mac, Control+D is the usual Fill Down shortcut. Check several results near the top, middle, and bottom of the list before relying on the full column.
Compare the results with known records. Verify the extracted parts and age against a few people whose dates of birth you are authorized to use. Include a record with a leading-zero month or day, one near a birthday, and one with a year near the century cutoff. If the ID format is different, edit the character positions and century rule rather than accepting an age that merely looks plausible. For reporting, you can keep the helper columns visible for auditing or hide them after validation; the age formula depends on them, so do not delete them.

Common Mistakes

Assuming every ID embeds a birth date. The formula is not a universal ID decoder. It only works for a verified YYMMDD field at the assumed position. Some identification numbers contain check digits, assigned sequences, or encoded dates that require a separate rule.

Using the wrong century. A two-digit year does not identify a century on its own. The example’s cutoff works for a particular age range, not every population or historical dataset. A person born in 1920 would be incorrectly interpreted by that rule, for instance. Use an issuer-provided century indicator, a known age limit, or a full four-digit year wherever possible.

Losing leading zeros. If an ID starts with 0 and Excel stores it as a number, the zero disappears. LEFT and MID then read the wrong characters. Import the field as Text or re-import the original data; formatting the already-shortened value as Text cannot recover the missing digit.

Trusting DATE to reject impossible dates. DATE normalizes some out-of-range entries instead of reporting an error. That is why the age formula compares the resulting year, month, and day with the extracted parts. Do not remove those checks unless another validation method replaces them.

Confusing age with a decimal year count. DATEDIF with the unit Y returns completed whole years, not a fraction such as 36.7. That is normally the desired meaning of age. If you need age on a past or future reporting date, replace TODAY() with a cell containing that date and use the same date consistently.

Tips

Keep a copy of the original ID values and the source-format documentation. A separate helper area makes the calculation easier to audit: you can see whether the error comes from extracting characters, interpreting the century, or validating the date. If the workbook will be maintained by other people, add a note explaining the ID layout and century cutoff beside the headings.

For a fixed reporting date, enter the date in a clearly labeled cell such as G1 and use that cell in place of TODAY() in the age calculation. This gives every row the same as-of date and prevents a report from changing unexpectedly when it is opened later. Make sure G1 contains a real Excel date, not date-looking text. For a daily-updating age list, TODAY() is appropriate; Excel recalculates it when the workbook recalculates.

Use a filter on the Age column to find “Check ID” values, then inspect the corresponding ID and helper cells. Do not silently replace those results with zero: zero could be mistaken for a valid age. If you need only final ages in another system, copy the validated age results and use Paste Special > Values, while retaining a protected or separate copy of the formulas for review.

Because IDs can be sensitive personal information, limit who can access the workbook and store only the fields needed for the task. Hiding the ID column is not the same as securing it. Follow your organization’s retention and privacy rules, and avoid sharing a workbook containing real identifiers when a de-identified sample will do.

Troubleshooting

The result says “Check ID.” Confirm that A2 contains at least six characters and that the first six are really YYMMDD. Inspect B2, C2, and D2 separately. A text value with spaces or punctuation can shift the positions; remove only characters that the issuer’s format says are not part of the encoded date. Also check whether the ID represents a future date under the chosen century rule.

The age is plausible but one year off. Check the month and day extracted from the ID, the century assignment, and the date on which the age is being calculated. Completed age increases on the birthday, not on January 1. If you compare the result with a different reporting date, replace TODAY() with that actual date.

The birth date displays as a number. The date may still be correct: Excel stores dates as serial values. Apply a date format to E2 using Home > Number Format. If the cell shows a formula instead of a result, check that Show Formulas is off and that the cell is not formatted as Text; change the format to General, re-enter the formula, and press Enter.

The formula produces a syntax error. Confirm that formulas begin with an equals sign and that the quotation marks around Y and Check ID are straight double quotes. Some regional settings use semicolons instead of commas as argument separators; if Excel flags the commas, use the separator required by your installation. Excel for Mac and Excel for the web support these functions, but localization settings can still affect separators and function names.

Every row repeats the same age or date. Check whether the formulas refer to A2, B2, and the other row-2 cells with ordinary relative references, then fill down again. If you copied values instead of formulas, restore the formulas from the first row. Also confirm that calculation is set to Automatic under Formulas > Calculation Options if TODAY() is not updating.

Frequently Asked Questions

Can Excel calculate age from any ID number?
No. It can calculate age only when the ID contains a date of birth in a known, documented format. If the number is just an assigned identifier, you need a separate date-of-birth field.

Can I do this without helper columns?
Yes, but helper columns make the extracted year, month, day, and date easier to check. A single long formula is harder to troubleshoot, and it still needs a reliable century rule and validation. For a maintained list, the visible intermediate steps are usually safer.

Does the formula return age in months or decimal years?
No. DATEDIF with Y returns the number of whole years completed. A person who has not yet reached their next birthday remains at the previous age; the formula does not return months or a decimal fraction.

Will this work if the ID is stored as a number?
It may work if the ID has no leading zero and Excel has preserved every digit, but long numeric IDs can lose precision and leading zeros are not retained. Store or import identifiers as Text. IDs are labels, not values to calculate with.

How do I handle a different date order?
Change the extraction positions and the order passed to DATE to match the official format. For a DDMMYY date, for example, extract the day, month, and year from their documented positions, then pass the full year first, month second, and day third to DATE. Test against verified examples before filling the formula down.

Is the example suitable for legal or official age checks?
Not automatically. Century cutoffs, leap-day birthdays, and the organization’s definition of age may affect official decisions. Confirm the governing rules and validate the ID format with the issuing authority before using spreadsheet results for eligibility, compliance, or other consequential decisions.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.