Return the number of days between two dates as a year fraction
Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
The YEARFRAC Function is categorized under Excel DATE/TIME functions. YEARFRAC will return the number of days between two dates as a year fraction, in decimal form, in Excel.
In financial analysis, one can use the function to, for example, calculate a portion of annual revenues.
=YEARFRAC(start_date, end_date, [basis])
The YEARFRAC function uses the following arguments:
Start_date (required argument) – This is the start of the period. The function includes the start date in calculations.
End_date (required argument) – This is the end of the period. The function also includes the end date in calculations.
[basis] (optional argument) – Specifies the type of day count basis to be used.
Various possible values of [basis] and their meanings are:
How to use the YEARFRAC Function in Excel?
As a worksheet function, YEARFRAC can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let’s consider a few examples:
Example – A few simple date differences
Let’s see a few dates and how the function provides results:
The formula used was:
The results in Excel are shown in the screenshot below:
Applied Example on how to use the YEARFRAC Function
Let’s assume we are handling the HR department and are processing data from our employee database, shown below. We realize that we have all of our employees’ dates of birth but we want to find out their age ‘value’ today. We can use the YEARFRAC function along with the INT and TODAY functions to do this. We insert a column next to the Date of Birth, label it “age”, and insert the following formula:
The formula used looks like this:
The INT function will output the age value as an integer, which is the day-to-day convention. Now if you want to have a more precise value for age, such as a decimal, just remove the INT() part of the function. Excel will use whole days between two dates. As all dates are simply serial numbers, the process is straightforward in Excel.
We get the results below:
If you replace the TODAY function with another date you get the age for that person on a specific date.
For example, if we are holding a competition for people aged below 35 years old, we can use the following formula:
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Already have a Self-Study or Full-Immersion membership? Log in
Access Exclusive Templates
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.