How to use the Excel Years Fraction formula to calculate the fraction of a year between two dates
Excel Years Fraction formula
Excel years fraction formula allows you to determine the fraction of a year based on two dates.
Activate the function wizard, it must be on Date and Time- you need to find YEARFRAC – this returns the year fraction representing the number of whole days between Start_date and End_date. Click OK. Insert the start date- 1st Jan 09 (A11) and the end date 1st Jan 10 (B11). You can also insert the Basis – which is the type of day count basis to use. When I click OK you’ll see I get an answer – between the 1st Jan 09 and the 1st Jan 10 is a whole (one) year. If I copy this down, you’ll see that 15th June to 5th July is 0.06 of a year, and the 1st Jan to 1st June is 0.42 of a year. What is quite useful is we can then take the Year Frac numbers and multiply them by 12 (=E11*12) to determine the number of months between the two dates. In the first case the answer is 12 and if we copy down we see that between 15th June and 5th July it is 0.67 (just over half a month) and in the third case you will see it is 5.