Explanation
The DATEDIF function (Date + Dif) is a bit of an anomaly in Excel. A compatibility function that comes originally from Lotus 1-2-3, Excel will not help supply arguments when the function is entered. However, DATEDIF works in all modern versions of Excel and is a useful function for calculating the interval between two dates in years, months, and days.
In the example shown, the goal is to calculate age in years. The formula in E5 is:
=DATEDIF(D5,TODAY(),"y")
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. The DATEDIF function supports several options here , but for this example the goal is age in whole years, so we use “y” to specify complete years.
At this point, we can rewrite the formula as below:
=DATEDIF("15-May-2001","24-Nov-2020", "y")
Because Excel dates are actually serial numbers , the raw values are:
=DATEDIF(37026,44159,"y")
With these inputs, DATEDIF returns 19 as a final result.
Age on specific date
To calculate age on a specific date, replace the TODAY function with the target date. An easy and safe way to hardcode a specific date into a formula is to use the DATE function . For example, to calculate age as of January 1, 2021, you can use a formula like this:
=DATEDIF(D5,DATE(2022,1,1),"y") // returns 20
This formula will return Michael Chang’s age on January 1, 2022, which is 20.
Adult or Minor
To check a birthdate and return “Minor” or “Adult”, you can wrap the formula in the IF function like so:
=IF(DATEDIF(D5,TODAY(),"y")<18,"Minor","Adult")
The above formula is an example of nesting . Replace 18 with whatever age is appropriate.
Age in years, months, and days
To calculate age in years, months, and days, use three instances of DATEDIF like this:
=DATEDIF(A1,TODAY(),"y")&"y "&DATEDIF(A1,TODAY(),"ym")&"m "&DATEDIF(A1,TODAY(),"md")&"d"
The first instance of DATEDIF returns years, the second instance returns months, and the third instance returns days. This is an example of concatenation , and the result is a text string like this:
19y 6m 9d
Note start and end dates remain the same in all three DATEDIFs; only the unit is changed.
YEARFRAC with INT
Another option for calculating age from birthdate uses the YEARFRAC function together with the INT function in a formula like this:
=INT(YEARFRAC(D5,TODAY()))
YEARFRAC calculates a decimal number representing the fraction of a year between two dates. To work out the fraction of a year as a decimal value, Excel uses days between two dates. As above, the birthdate is provided as the start_date from cell D5, and today’s date is supplied as the end_date, courtesy of the TODAY function.
With a current date of November 24, 2020, the result from YEARFRAC for Michael Chang is:
19.5290896646133
Next, the INT function takes over and rounds down that number to the integer value, which is the number 19.
=INT(19.5290896646133) // returns 19
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). I’m not sure exactly why this happens, but it is related to how YEARFRAC uses days to determine fractional years, which is controlled by the basis argument . For example:
=YEARFRAC(DATE(1960,6,30),DATE(1962,6,30),1) // 1.998, should be 2
=YEARFRAC(DATE(1960,3,3),DATE(1964,3,3),1) // 3.998, should be 4
The bottom line is that the date DATEDIF formula is a safer and easier option when the goal is to report age in whole years.
Explanation
The DATE function builds dates from separate year, month, and day values. One of its tricks is the ability to roll forward to correct dates when given days and months that are “out of range”. For example, DATE returns April 9, 2016, with the following arguments:
=DATE(2016,1,100)
There is no 100th day in January, so DATE simply moves forward 100 days from January 1 and returns the correct date.
The formula on this page takes advantage of this behavior. The year is assumed to be 2015 in this case, so 2015 is hard-coded for the year, and 1 is used for the month. The day value comes from column B, and the DATE function calculates the date as explained above.
Extracting a year value from a Julian date
If you have a date in a Julian format, for example, 10015, where the format is “dddyy”, you can adapt the formula as follows:
=DATE(RIGHT(A1,2),1,LEFT(A1,3))
Here, we use RIGHT to extract the 2 characters from the right for the year , and LEFT to extract 3 characters from the left for the day . The month is supplied as 1, as in the first example.
Explanation
Working from the inside out, we use the DATEDIF function to calculate how many complete years are between the original anniversary date and the “as of” date, where the as of date is any date after the anniversary date:
DATEDIF(B5,C5,"y")
Note: in this case, we are arbitrarily fixing the “as of” date as June 1, 2017 in all examples.
Because we are interested in the next anniversary date, we add 1 to the DATEDIF result, then multiply by 12 to convert to years to months.
Next, the month value goes into the EDATE function, with the original date from column B. The EDATE function rolls the original date forward by the number of months given in the previous step which creates the next upcoming anniversary date.
As of today
To calculate the next anniversary as of today, use the TODAY() function for the “as of” date:
=EDATE(date,(DATEDIF(date,TODAY(),"y")+1)*12)
