How to calculate age in Excel

Excel 2016
To calculate the age of a person, a pet, a project, or even some device, you need to compute a number of complete years. The issue is that it doesn't seem enough to calculate a year's difference. Excel proposes a very useful formula that can help you to calculate that very fast.

The formula DATEDIF calculates days, months or years between two dates:

= DATEDIF (<start date>, <end date>, unit)

The parameter unit can be:

  • "Y" - The number of full years in the period.

    To calculate an age:

    = DATEDIF (<start date>, TODAY (), "y")

    Number of complete years in the period in Excel 365

    Note: The formula TODAY () returns the current date. Sure, you can enter the end date to calculate age, not for the current date.

  • "YM" - The difference in months between <start date> and <end date> ignoring years. So, it looks like Excel changes a year for <start date> to the same as in <end date> and calculates the difference in days.

    For example:

    = DATEDIF (<start date>, TODAY (), "ym"):

    Number of complete years and months in the period in Excel 365
  • "MD" - The difference in days between <start date> and <end date> ignoring months and years. So, it looks like Excel changes year and month for <start date> to the same as in <end date> and calculates the difference in days.

    For example:

    = DATEDIF (<start date>, TODAY (), "md"):

    Full number of complete years, months and days in the period in Excel 365

    Note: You can add IF to ignore months and days with 0:

    = IF (<comparison>, <result if comparison is TRUE>, <result if comparison is FALSE>)

    Number of complete years, months and days in the period in Excel 365

See also this tip in French: Comment calculer l’âge dans Excel.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more