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.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.