The following article introduces you to the DATEDIF function - one of the functions in the date and time group function is very popular in Excel.
Description: The function calculates the number of days, months and years between two dates in Excel. Functions are useful in formulas for calculating age numbers.
Syntax: DATEDIF (start_date, end_date, unit)
Inside:
- start_date: The first day or representing the first period of time you want to calculate, the date can be entered as text string or as a serial number.
- end_date: The last day or represent the last period you want to count.
- unit: The type of information you want to return, including the following values:
+ unit = 'Y' -> Returns the number of years in the period.
+ unit = 'M' -> Returns the number of months in the time period.
+ unit = 'D' -> Returns the number of days in the period.
+ unit = 'MD' -> Returns the difference between start_date and end_date dates , regardless of the month and year.
+ unit = 'YM' -> Returns the difference between months in start_date and end_date , regardless of the day and year.
+ unit = 'YD' -> Returns the difference between dates in start_date and end_date , regardless of the year.
Attention:
- Date values are stored as serial numbers in Excel.
- DATEDIF function is very useful in calculating age.
For example:
1. Calculate time intervals based on criteria according to the table described below with Unit options.
- In the cell to calculate, enter the formula: DATEDIF (E7, F7, G7).
- Press Enter -> return value is:
- Similarly copying the formula for the remaining values results:
2. Use the DATEDIF function to calculate the age of the following employees:
- In the cell to calculate, enter the formula: = DATEDIF (G7, NOW (), "Y").
- Press Enter -> the employee's age is:
- Similarly copying the formula for the remaining values results:
Above are instructions and some specific examples when using the DATEDIF function .
Good luck!