Calculate Age with DATEDIF: Excel Formula Explained

This calculator works out a person’s age in years, months, and days using the same DATEDIF logic found in Excel and Google Sheets. You enter a birth date and a reference date (usually today), and you get back the completed years, months, and days between them. The result is the exact calendar age, not a rounded decimal.

Formula

The DATEDIF function calculates the difference between two dates in whole units. For age, the standard approach uses three separate DATEDIF calls:

• Years: =DATEDIF(start, end, "Y") — completed full years.
• Months: =DATEDIF(start, end, "YM") — remaining months after the last full year.
• Days: =DATEDIF(start, end, "MD") — remaining days after the last full month.

In the calculator above, the same logic is implemented using average day counts: one year is treated as 365.2425 days (the average length of a Gregorian calendar year), one month as 30.4375 days (365.2425 / 12). The floor function drops any fractional part, giving whole completed units.

Worked Example

Using the default inputs: birth date 1990-06-15 and reference date 2024-09-01.

1. Total days between the dates: 34 years × 365.2425 = 12,418.245 days, plus the extra days from June 15 to September 1 (78 days), giving approximately 12,496 days.
2. Years: floor(12,496 / 365.2425) = floor(34.21) = 34.
3. Months: floor(12,496 / 30.4375) = floor(410.5) = 410 months total. But the calculator shows the remaining months after the full years: 410 − (34 × 12) = 2 months.
4. Days: the calculator shows the remaining days after the full months: 78 days − (2 × 30.4375) = 78 − 60.875 = 17.125, floored to 17 days.

So the age is 34 years, 2 months, and 17 days.

How DATEDIF Works in Excel and Google Sheets

DATEDIF is a hidden function in Excel — it exists for backward compatibility but doesn’t appear in the function list. You type it manually. Google Sheets includes it in the normal list. The syntax is:

DATEDIF(start_date, end_date, unit)

The unit argument controls what is returned:

• "Y" — whole years
• "M" — whole months
• "D" — whole days
• "YM" — months remaining after the last whole year
• "YD" — days remaining after the last whole year
• "MD" — days remaining after the last whole month

The function counts completed units. For example, if someone was born on January 31 and the reference date is February 28 (non-leap year), DATEDIF with “MD” returns 28, because the 31st never occurs in February. This can surprise users, but it follows the rule of counting only actual calendar days that exist.

Choosing the Right Unit for Your Need

For a simple age in years, use "Y". For a full breakdown, combine "Y", "YM", and "MD". If you need the exact number of days between two dates (for a contract or lease), use "D". The "YD" unit gives days ignoring years, which is useful for annual events like birthdays.

For legal age (e.g., voting, drinking), you only need the year count. For medical or developmental milestones, the months and days matter. The calculator above gives all three at once, so you don’t have to decide in advance.

Common Mistakes and Misconceptions

• Using the wrong unit: Mixing up "YM" (months after a year) with "M" (total months) gives wildly different numbers. For a 34-year-old, total months are 410, while remaining months are 2.
• Rounding instead of flooring: DATEDIF always truncates. If you use ROUND, you might get 35 years when the person is still 34.
• Date format confusion: In Excel, dates are stored as serial numbers. If you type a date as text, DATEDIF may return an error. Always use actual date cells.
• Leap years: DATEDIF handles February 29 correctly. For example, from 2020-02-29 to 2021-02-28 is exactly 365 days, not 366.
• Start date after end date: DATEDIF returns an error (#NUM!) if the start is later than the end. The calculator above assumes the birth date is before the reference date.

Limitations of the DATEDIF Approach

DATEDIF counts completed calendar units, which is the standard for age. However, it does not account for time of day. If you need precise age to the hour (e.g., for medication dosing), you must include timestamps and use a different method. Also, the function is not available in older versions of Excel (before 2000) and may not work in some third-party spreadsheet tools that don’t implement it.

For legal documents, always verify the result with a secondary method, such as counting days manually or using a dedicated age calculator. The formula is reliable, but date entry errors are common.

Alternatives to DATEDIF

If DATEDIF is not available, you can use arithmetic:

• Years: =YEAR(end) - YEAR(start) - IF(DATE(YEAR(end), MONTH(start), DAY(start)) > end, 1, 0)
• Days: =end - start (Excel stores dates as numbers)
• Months: =(YEAR(end)-YEAR(start))*12 + MONTH(end)-MONTH(start) — then adjust if the day hasn’t been reached.

These formulas are more verbose but work in any spreadsheet that handles dates as numbers. The calculator above uses a simplified average-day method, which is accurate to within a day for most dates.

Frequently Asked Questions

Why does DATEDIF not appear in Excel’s function list?

It’s a legacy function from Lotus 1-2-3, kept for compatibility. Microsoft hides it from the formula autocomplete to avoid confusing new users, but it still works if you type it manually. Google Sheets includes it normally.

What does “MD” in DATEDIF mean?

It stands for “month difference� — it returns the number of days between the start and end dates, ignoring years and months. For example, from January 5 to March 10, it returns 5 (the 5th to the 10th of the month).

How do I calculate age in years and months only?

Use =DATEDIF(start, end, "Y") for years and =DATEDIF(start, end, "YM") for months. Combine them in a text string: =DATEDIF(start, end, "Y") & " years, " & DATEDIF(start, end, "YM") & " months".

Does DATEDIF work with dates before 1900?

Excel’s date system starts at January 1, 1900. Dates before that are not recognized as dates. Google Sheets uses a different serial number system and can handle dates from 1900-01-01 onward as well. For older dates, you would need a custom solution.

Why does my DATEDIF return #NUM!?

The most common cause is that the start date is after the end date. Check both cells. Also, ensure the dates are actual date values, not text. If a cell contains text like “01/01/2020” as a string, DATEDIF will fail.

Can I use DATEDIF to calculate someone’s age in days only?

Yes, use =DATEDIF(start, end, "D") or simply subtract the two dates: =end - start. Both give the same whole number of days.

Related

• Find Out Where a Photo Was Taken: EXIF & More
• Control D Profiles & Custom DNS Rules Setup
• Grow a Garden Fall Pets Shop Guide?

in start | Birth date | - | 1990-06-15 | 0
in end | Reference date | - | 2024-09-01 | 0
out years | Years | yr | floor((end-start)/365.2425) | 0
out months | Months | mo | floor((end-start)/30.4375) | 0
out days | Days | d | floor((end-start)) | 0