How to Calculate Age Using DATEDIF Functions in Excel — The DATEDIF function is a function in Microsoft Excel that is used to calculates the number of days, months, or years between two dates.
The Excel DATEDIF function allows you to calculate the number of years, months, or days between two predefined dates. This function is commonly used to calculate age or work life in Excel.
When you type =DATEDIF in the formula bar, you will not find the DATEDIF function in the excel formula or function list. Even if the auto-complete feature doesn’t bring up the DATEDIF function, it doesn’t mean that your excel has a problem or the datedif function isn’t there and you can’t use it.
This DATEDIF function is basically an Excel function provided for compatibility with Lotus 1-2-3, and can still be used in all versions of excel including Microsoft Excel 2007, 2010, 2013, 2016 and newer versions of office excel.
So, to use the DATEDIF function that is not on the insert function menu, you must manually type this DATEDIF formula by directly typing the function name in the formula bar or active cell where you will use this excel formula.
How to Use DATEDIF function
How to use the DATEDIF function is as follows:
start_date : A date that represents the first, or starting date of a given period. Dates may be entered as text strings within quotation marks (for example, “2001/1/30”), as serial numbers (for example, 36921, which represents January 30, 2001, if you’re using the 1900 date system), or as the results of other formulas or functions (for example, DATEVALUE(“2001/1/30”)).
end date : A date that represents the last, or ending, date of the period.
unit has some variations:
“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.
If you want to calculate age completely, for example 2 years 10 months 1 day, you have to use 3 times the DATEDIF function with 3 different parameters.