How to use the Excel Years Fraction formula to calculate the fraction of a year between two dates.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
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.