YEARFRAC Excel Function


« Back to videos

Additional Information:

This tutorial shows you how to use the YearFrac function to calculate ages in Microsoft Excel. As years can be of variable length, think leap years, this function represents the most accurate way of measuring the difference in years between two dates.

The simplest version of the function takes the form "=YEARFRAC(StartDate, EndDate)", where the start and end dates would normally be cell references e.g. if "1st January 2000" is in cell A1, StartDate can be replaced with A1. However, you can use anything that Excel would recognise as a date, so EndDate could be replaced with TODAY() if you wished to know someone or something's current age.

A more sophisticated version of the function takes the form "=YEARFRAC(StartDate, EndDate, Basis)" where the basis models the spread of days throughout the year. If omitted, it assumes you are happy to consider the year as 12 months of 30 days, which is fine for most purposes. In the video, a Basis of 1 is used, which gives you a true fraction.

Note the result will nearly always be a fraction. Assuming you want the number of full years, you should surround the entire function with INT() e.g. "=INT(YEARFRAC(1st January 2000, 12th January 2010)" would return a value of 10. As soon as you are rounding to the nearest year, the basis becomes entirely irrelevant.