Excel Formula Help - YEARFRAC for accurate date calculations
Using YEARFRAC in Microsoft Excel allows us to accurately calculate dates taking into account leap years.
Take this simple example in which we would like to write a formula that gives us the age of a list of celebrities through using their year of birth. The formula =(TODAY()-
Excel returns the following
However, this result is incorrect as the formula is not able to fully account for leap years.
Instead, we should use use Excel's YEARFRAC function. In the relevant cell, in this case D5, type =YEARFRAC(C5,TODAY(),1)
As we see this provides the exact figure, to numerous decimal places, for the age of the celebrity. Importantly, YEARFRAC takes into account leap years, a fact which can be seen by the difference in the results for each formula; 28.82191781 vs 28.80087607.
To return a more realistic number with our YEARFRAC functions, with no decimal places, we can simply wrap the formula in the ROUNDDOWN function by edititing it in the following way: =ROUNDDOWN(YEARFRAC(C5,TODAY(),1),0) as per the example:
We are now ready to copy down and receive the full list of ages.
To get more help will all of your Excel tasks, contact our experts. You can also learn more about YEARFRAC and other data functions on the Microsoft Excel Help pages here.
