Explanation
TheDATEDIF function(Date + Dif) is a bit of an anomaly in Excel.
In the example shown, the goal is to calculate age in years.
The formula in E5 is:
The first two arguments for DATEDIF are start_date and end_date.
The start date comes from cell D5 (May 15, 2001) in the example.
The end date is generated with the TODAY function.
TODAY always returns the current date in Excel.
As of this writing, the current date is November 24, 2020.
The last argument in DATEDIF specifies the time unit.
An easy and safe way to hardcode a specific date into a formula is to use theDATE function.
Replace 18 with whatever age is appropriate.
To work out the fraction of a year as a decimal value, Excel uses days between two dates.
This formula appears perfectly logical and it works fine in most cases.
However, YEARFRAC can return a number that isn’t correct on anniversary dates (birthdays).
The DATEDIF (Date + Dif) function is a “compatibility” function that comes from Lotus 1-2-3.
The TODAY function takes no arguments.
you could format the value returned by TODAY with a datenumber format.
YEARFRAC Function
The Excel YEARFRAC function returns a decimal value that represents fractional years between two dates.
Note that negative numbers becomemore negative.
For example, while INT(10.8) returns 10, INT(-10.8) returns -11.
