How to calculate an 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 enough to calculate a year’s difference. Excel proposes 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 complete years in the period.

    To calculate an age:

    = DATEDIF (<start date>, TODAY(), “y”)

    Number of complete years in the period in Excel 2016

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

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

    For example:

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

    Number of complete years and months in the period in Excel 2016
  • "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 difference in days.

    For example:

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

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

    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 2016

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