One of the limitations of subtracting dates in Excel is that the application can only give you the number of days, the number of months, or the number of years individually, not a combined number.
Luckily, Microsoft has included a built-in Excel feature to give you the exact difference between two dates in a worksheet. Learn how to accurately calculate time interval between two dates in Excel.
Using the YEARFRAC function in Excel
Using the YEARFRAC function, you can calculate the exact difference between two dates, because unlike other methods that return an integer result, this function returns a decimal result to indicate the fractions of a year.
However, the YEARFRAC feature requires a little more knowledge than most other features. The basic formula for using this function is:
= YEARFRAC (start_date, end_date, basis)
Start_date is the first variable date, End_date is the second variable date, and the basis is the assumption that Excel should return the result of the calculation. This is the basis with which you must be careful when using the YEARFRAC function.
Let’s say you have an Excel sheet that looks something like this and you want to calculate the exact difference between two dates in A1 and A2:
Rounded to two decimal places, Excel returned 3.16 years using the YEAR function. However, since we did not include the base variable in the equation, Excel assumed that there are exactly 30 days in each month, and the total length of the year is only 360 days.
There are five values ??you can use for the base variable, each of which corresponds to a different assumption about the duration of one year.
According to the reference document, omitting or using a value of 0 for the base variable forces Excel to use the US NASD standard of 30-day months and 360-day years.
This may seem odd until you realize that many financial calculations are based on these assumptions. All possible values ??for the base variable include:
- 0 – US NASD, 30 days in months / 360 days in years
- 1 – Actual days in months / Actual days in years
- 2 – Actual days in months / 360 days in years
- 3 – Actual days in months / 365 days in years
- 4 – European 30 days in months / 360 days in years
Note that the base variable, which will return the most accurate number between two dates, is 1. Below are the results using each of the base variable values:
While some of the values ??for the underlying variable may seem odd, different combinations of one-month and one-year assumptions are used in several areas such as economics, finance, and operations management.
To stay consistent between months with different numbers of days (for example, February versus March) and between years with different numbers of days (think of leap years and calendar years), these professions often make strange assumptions that the average person would not make.
It is especially useful for the financier to use the assumptions provided by the underlying variable to calculate annual and interest rates based on different interest scenarios. Interest can be calculated continuously, daily, weekly, monthly, annually, or even over several years.
With the assumptions built into the YEARFRAC function, you can be sure that your calculations are accurate and comparable to other calculations using the same assumptions.
As stated above, a value of 1 for the base variable is technically the most accurate. When in doubt, select 1 if you are unsure of the assumptions you want to make about the length of the month and year. Enjoy!
–