YEARFRAC Function in Use
The nice thing about the YEARFRAC function is that it takes partial years into account. The best way to explain this further is to look at an example. Suppose we want to calculate the number of years between the dates shown in Column A and Column B in the spreadsheet below. (Click any image for a larger view.)
In this example, our start_date is 4/7/2006 (cell A2) and our end_date is 1/31/2009 (cell B2). We’ll use a basis value of 1 in order to calculate the true difference between the two dates. Our function can be stated as:
=YEARFRAC(A2, B2, 1)
Note that the order of the dates doesn’t matter. Even if the so-called start_date is more recent than the end_date, the YEARFRAC function will still return a positive value.
What if you want the difference to be stated in terms of months instead of years? In this case, all you have to do is multiply the result by 12 (the number of months in a year).
=12 * YEARFRAC(A2, B2, 1)
If you want the final answer to be represented in terms of days, it’s still better to use the basic subtraction method described in Subtracting Dates in Microsoft Excel (see the link to this article in the first section).
For more tips and tricks, browse through Bright Hub’s library of Microsoft Excel tutorials and user guides. Learn about lookup and reference functions, how to create charts and graphs, and more. Additional articles are being added on a regular basis, so check back often.