One of the easiest ways to calculate the age of anything is to use Excel’s DATEDIF function. This mysterious function doesn’t appear in Excel’s Help files, but it has been around since Excel 2000. This function makes calculating any kind of date comparisons a breeze.
To calculate a person’s age using the DATEDIF function, you can enter a formula like this:
=DATEDIF("5/16/1972",TODAY(),"y")
You can, of course, reference a cell that contains a date:
=DATEDIF(B4,TODAY(),"y")
The DATEDIF function calculates the number of days, months, or years between two dates. It requires three arguments: a start date, an end date, and a time unit.
The time units are defined by a series of codes, which are listed in the table.
| Code | What It Returns |
|---|---|
| "y" | The number of complete years in the period. |
| "m" | The number of complete months in the period. |
| "d" | The number of days in the period. |
| "md" | The difference between the days in start_date and end_date. The months and years of the dates are ignored. |
| "ym" | The difference between the months in start_date and end_date. The days and years of the dates are ignored. |
| "yd" | The difference between the days of start_date and end_date. The years of the dates are ignored. |
Using these time codes, you can easily calculate the number of years, months, and days between two dates. If someone was born on May 16, 1972, you could find that person’s age in year, months and days using these respective formulas:
=DATEDIF("5/16/1972",TODAY(),"y")
=DATEDIF("5/16/1972",TODAY(),"m")
=DATEDIF("5/16/1972",TODAY(),"d")