Calculate Age in Months in Excel: Formulas and Tips

To calculate completed age in months in Excel, put a birth date in A2 and enter =DATEDIF(A2,TODAY(),"m") in another cell. Excel returns the number of whole months from the birth date to today. You will need a real Excel date value—not text—and can replace TODAY() with a cell containing a chosen as-of date when you need a fixed result.

What You’ll Need

A worksheet with a birth date for each person or record. For example, put the first date in A2 and any additional dates below it.
A result column where you can enter the formula, such as B2.
Excel for Microsoft 365 on Windows. The DATEDIF function also works in supported Excel versions on Mac and Excel for the web. It is an older compatibility function and may not appear in Excel’s formula autocomplete, but you can type it directly.
Optionally, a separate cell for an as-of date. This is useful for reports that must show age as of a past or future date rather than changing every day.

Step-by-Step

Set up the date column. Enter each person’s date of birth in column A, with the first value in A2 and one date per row. A date such as 15-Jan-2000 is easier to read than a string of digits, but the display style does not determine whether Excel recognizes it as a date. To check, select a date cell and use Home > Number Format to choose Short Date. If it changes to a recognizable date, it is likely stored as a date value. If it remains text or does not behave like a date, correct it before calculating.
Choose the result cell. Click B2, next to the first birth date. Keeping results in a separate column means the original dates remain available for checking, sorting, or correcting. Add a heading such as Completed months in B1 so other people know what the number represents.
Enter the basic formula. With the date of birth in A2 and the result in B2, enter =DATEDIF(A2,TODAY(),"m"). Press Enter. The “m” unit asks for the number of complete months between the two dates. TODAY() supplies the current date, so the result can change when the workbook recalculates on a later day. DATEDIF counts completed months, not every calendar month touched by the date range.
Check the result against a known example. With a birth date of 15-Jan-2000 in A2 and a current date of 2-Nov-2026, the formula returns 321. There are 321 complete months from 15-Jan-2000 to 15-Oct-2026; the next complete month is not reached until 15-Nov-2026. Your own result will depend on the date when Excel recalculates. This check helps distinguish full months from a rough conversion based on days.
Use a fixed as-of date when needed. For a report that must remain consistent, enter the comparison date in B1 and put the result in C2. With the birth date in A2 and the fixed as-of date in B1, use =DATEDIF(A2,$B$1,"m"). The dollar signs lock the reference to B1 when you copy the formula down. For example, with A2 set to 15-Jan-2000 and B1 set to 2-Nov-2026, this returns 321. Change B1 to recalculate every row against a different date.
Fill the formula down the list. If the dates are in A2:A20, enter the formula in B2, select B2, and drag the small fill handle at the lower-right corner of the cell down through B20. Excel adjusts A2 to A3, A4, and so on. You can also copy B2, select B3:B20, and paste. Check the first and last copied rows to confirm that each result refers to the date on the same row.
Show years and leftover months if that is clearer. A large total such as 321 may be less readable than 26 years and 9 months. With the birth date in A2, enter this in B2: =DATEDIF(A2,TODAY(),"y")&" years, "&DATEDIF(A2,TODAY(),"ym")&" months". For the example date 15-Jan-2000 evaluated on 2-Nov-2026, the display is 26 years, 9 months. This is text for display, not a numeric total; keep the basic months formula in a separate column if you need to sort or calculate with the number.
Protect against blank or future dates. A blank cell should not be mistaken for a valid birth date, and a date later than today makes DATEDIF return an error. With a birth date in A2 and the result in B2, use =IF(A2="","",IF(A2>TODAY(),"Check date",DATEDIF(A2,TODAY(),"m"))). It leaves the result blank when A2 is blank and displays Check date for a future date. Otherwise, it returns completed months. If you are using a fixed comparison date, replace TODAY() in both places with the as-of-date cell reference.

Common Mistakes

Using the wrong unit. DATEDIF’s “m” unit returns whole completed months. The “y” unit returns completed years, and “ym” returns the months left after whole years are counted. Use “m” when you need the total number of months; do not use “ym” for a total.
Assuming a month is always 30 days. Calendar months have different lengths. Dividing elapsed days by 30, or multiplying age in years by 12, can give a different answer from counting completed calendar months. Use DATEDIF for whole months between dates. Use a day-based estimate only if your process specifically defines a month as a fixed number of days.
Expecting the answer to include part of a month. DATEDIF with “m” does not return a decimal or round up. If a person is 3 months and 20 days old, the result is 3 completed months. Decide whether the task calls for completed months or an estimate before choosing a method.
Entering dates as text. A value that looks like 15-Jan-2000 may have been imported as text. Excel may then return an error or calculate unexpectedly. Re-enter it as a date or convert the source values to real dates before filling down.
Forgetting that TODAY() changes. A formula using TODAY() is dynamic. A workbook opened later can show a different age, and recalculation settings can affect when the change appears. Use a fixed as-of date cell for snapshots, audits, and historical reports.

Tips

Use a clear heading such as Completed age (months) so readers do not confuse a whole-month result with a decimal age or an estimate.
Format birth dates consistently. Select the date cells and use Home > Number Format > Short Date, or choose a suitable custom date format through Format Cells. Formatting changes how valid dates appear; it does not turn text into dates by itself.
For repeatable reporting, type the reporting date into one clearly labeled cell and reference it with dollar signs in the formula. This makes it easy to reproduce a report later without relying on today’s date.
If you need to compare or summarize ages, keep the result as a number. The years-and-months display formula joins numbers with text, so it is better used as a presentation column than as the source for further arithmetic.
Be explicit about your rule for end-of-month birthdays. For example, DATEDIF counts a complete month only when the corresponding day has been reached; a birth date on the 31st can therefore behave differently around shorter months. If a business or legal rule defines this differently, document and implement that rule separately.

Troubleshooting

The formula returns #NUM!. The start date is later than the end date, or one of the dates is invalid. Check that the birth date is not in the future and that the as-of date is not earlier than the birth date. If future dates are possible in your data, use the blank-and-future-date check shown above.

The formula returns #VALUE! or an unexpected number. One or both date cells may contain text rather than Excel date values. Select the cell and change its format to Short Date; if the value does not behave like a date, re-enter it or use Excel’s conversion options for the imported data. Also check for stray spaces, swapped month and day values, or dates interpreted using a different regional format.

The result appears as a date instead of a month count. The result cell may be formatted as a date. Select the result cells and choose Home > Number Format > General or Number. DATEDIF returns a number, and a date format can make that number look like a calendar date.

The answer is one month lower than expected. The formula counts completed months, so the day of month matters. If the as-of date falls before the corresponding day in the birth month, the next month is not complete yet. Review both dates and confirm whether your requirement is completed months, rounded months, or a different age convention.

The result does not update. TODAY() updates when Excel recalculates the workbook. In Excel for Windows, check Formulas > Calculation Options and select Automatic if appropriate. You can also press F9 to recalculate. On Mac, calculation options are available from Formulas > Calculation Options; the available keyboard shortcut can differ by keyboard and Excel settings. If the workbook intentionally uses a fixed date in a cell, edit that cell instead of expecting the result to change automatically.

Frequently Asked Questions

Does DATEDIF calculate age in months accurately?

It calculates the number of complete calendar months between two valid dates. That is accurate when your definition of age in months is full months elapsed. It does not return partial months or a decimal estimate, and dates near the end of a month may need special consideration if your organization uses a different rule.

Can I calculate age in months as of a date other than today?

Yes. Put the comparison date in a cell and use that cell as DATEDIF’s end date. An absolute reference such as $B$1 keeps the chosen date fixed when you copy the formula down. This is the better approach for reports that must be reproducible.

Why is DATEDIF missing from Excel’s function suggestions?

DATEDIF is retained for compatibility with older spreadsheet files, so Excel may not display it in autocomplete or the function wizard. You can still type the function directly into a cell, provided its date arguments are valid.

Can I get age in months as a decimal?

Not with DATEDIF’s “m” unit: it returns whole completed months. A decimal requires you to define how a partial month should be measured, such as using elapsed days divided by an agreed average month length. That is an estimate, not the same calendar-month calculation, so label it clearly.

Will this work in Excel for Mac or Excel for the web?

DATEDIF and TODAY() are available in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Ribbon names and shortcuts can vary slightly by platform, but the formula itself is the same.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.