Excel Guides

Calculating Fractions of Years in Excel

When working with dates in Excel, you may sometimes need to calculate fractions of years. For example, you may have a start date and an end date, and need to know how many years and days there are between the two dates. Or, you may need to calculate what percentage of a year has elapsed between two dates.

There are a few different ways to calculate fractions of years in Excel. One way is to use the YEARFRAC function. This function takes two arguments: a start date and an end date. The function will then return the number of years between the two dates as a decimal value.

For example, if your start date is January 1, 2016 and your end date is December 31, 2016, the YEARFRAC function will return 1.0 because there is exactly one year between those two dates.

If we change the end date to December 31, 2017, the YEARFRAC function will return 1.5 because there are 1.5 years between the two dates (2017 is a leap year, so there are 366 days in that year).

You can also use the DATEDIF function to calculate fractions of years. This function takes three arguments: a start date, an end date, and "y" for years. The function will then return the number of complete years between the two dates.

For example, if your start date is January 1, 2016 and your end date is December 31, 2016, the DATEDIF function will return 1 because there is exactly one complete year between those two dates.

If we change the end date to December 31, 2017, the DATEDIF function will return 2 because there are 2 complete years between the two dates (2017 is a leap year, so there are 366 days in that year).

You can also use the DATEDIF function to calculate other time periods besides years. For example, you can use "m" for months or "d" for days. Check out Microsoft's documentation on the DATEDIF function for more information.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.