在Excel(Excel)中减去日期的限制之一是应用程序只能分别给出天数、月数或年数，而不是组合数字。
幸运的是，Microsoft包含一个内置的Excel 函数(Excel function)，可以为您提供工作表中两个日期之间的精确差异。了解如何在Excel中精确计算两个日期之间的时间量。
在Excel中使用YEARFRAC 函数(YEARFRAC function)
使用YEARFRAC 函数(YEARFRAC function)，您可以计算两个日期之间的精确差异，因为与其他返回整数结果(integer result)的方法不同，此函数返回十进制结果以表示一年的分数。
然而， YEARFRAC 函数(YEARFRAC function)需要比大多数其他函数更多的知识。使用这个函数的基本公式是：
=YEARFRAC(start_date, end_date, basis)
start_date是第一个变量日期，end_date是第二个变量日期，基础(basis)是Excel应返回计算结果的假设。这是使用YEARFRAC 函数(YEARFRAC function)时必须小心的基础。
假设(Suppose)您有一个看起来像这样的Excel 工作表，并且您想要计算(Excel worksheet)A1 和 A2(A1 and A2)中两个日期之间的精确差异：
舍入到小数点后两位，Excel使用(Excel)YEARFRAC 函数(YEARFRAC function)返回 3.16 年的结果。但是，由于我们没有在等式中包含基础变量， (basis variable)Excel假设每个月正好有 30 天，因此一年的总长度只有 360 天。
您可以为基础(basis)变量使用五个值，每个值对应于关于一年长度的不同假设。
根据帮助文档(help document)，忽略或使用值 0 作为基础(basis)变量会强制Excel假定美国 NASD 标准(US NASD standard)为 30 天月和 360 天年。
这可能看起来很奇怪，直到您意识到许多财务计算都是在这些假设下进行的。基础(basis)变量的所有可能值包括：
-
0 – 美国 NASD 30 天月/360 天年
-
1 – 月份中的实际天数/年份中的实际天数
-
2 – 月份中的实际天数/年份中的 360 天
-
3 – 月份中的实际天数/年份中的 365 天
-
4 – 欧洲每月 30 天/每年 360 天
请注意，将返回两个日期之间最准确数字的基础变量的值是 1。下面是使用(basis)基础(basis)变量的每个值的结果：
尽管基(basis)变量的某些值可能看起来很奇怪，但在经济、金融和运营管理等多个领域中使用了关于一个月和一年长度的不同假设组合。
为了在不同天数的月份（想想2 月(February)与3 月(March)）和不同天数的年份之间（想想闰年与日历年）保持可比性，这些专业通常会做出普通人不会做出的奇怪假设。
对金融家特别有用的是使用基础(basis)变量提供的假设来根据不同的复利情景计算APR 和 APY 。(APRs and APYs)利息可以连续计算，每天，每周，每月，每年甚至跨越多年。
通过YEARFRAC 函数(YEARFRAC function)中内置的假设，您可以确保您的计算是准确的，并且可以与使用相同假设的其他计算进行比较。
如上所述，基础(basis)变量的值 1 在技术上是最准确的。如果有疑问，请选择 1，除非您确定要对一个月和一年的长度做出的假设。享受！
How to Use the YEARFRAC Function in Excel
One of the limitations of subtracting dates in Excel is that the application can only give you either the number of days, the number of months, or the number of years separately, rather than a combined number.
Luckily, Microsoft included a built in Excel function to give you precise differences between two dates in a worksheet. Learn how to calculate the amount of time between two dates with precision in Excel.
Using the YEARFRAC function in Excel
Using the YEARFRAC function, you can calculate the precise difference between two dates because unlike other methods that return an integer result, this function returns a decimal result to indicate fractions of a year.
The YEARFRAC function, however, requires a bit more knowledge than most other functions. 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 basis is the assumption under which Excel should return the result of the calculation. It’s the basis with which you must be careful when using the YEARFRAC function.
Suppose you have an Excel worksheet that looks something like this and you want to calculate the precise difference between the two dates in A1 and A2:
Rounded to two decimal places, Excel returned a result of 3.16 years using the YEARFRAC function. However, because we did not include the basis variable in the equation, Excel assumed that there are exactly 30 days in every month giving a total year’s length of just 360 days.
There are five values that you can use for the basis variable, each corresponding to a different assumption about the length of one year.
According to the help document, either omitting or using a value of 0 for the basis variable forces Excel to assume the US NASD standard of 30-day months and 360-day years.
This may seem strange until you realize that many financial calculations are made under these assumptions. All of the possible values for the basis variable include:
-
0 – US NASD 30 day months/360 day years
-
1 – Actual days in the months/Actual days in the years
-
2 – Actual days in the months/360 days in the years
-
3 – Actual days in the months/365 days in the years
-
4 – European 30 days in the months/360 days in the years
Notice that the value for the basis variable that will return the most accurate number between two dates is 1. Below are the results of using each of the values for the basis variable:
Although some of the values for the basis variable may seem strange, different combinations of assumptions about the length of one month and one year are used in several fields such as economics, finance, and operations management.
In order to remain comparable between months with different numbers of days (think February vs. March) and between years with different numbers of days (think leap years vs. calendar years), these professions often make strange assumptions that the average person would not.
Particularly useful to the financier is using the assumptions offered by the basis variable to calculate APRs and APYs based on different interest compounding scenarios. Interest can be calculated continuously, daily, weekly, monthly, yearly or even span multiple years.
With the assumptions built into the YEARFRAC function, you can be sure your calculations are accurate and comparable with other calculations using the same assumptions.
As stated above, a value of 1 for the basis variable is technically the most accurate. If in doubt, choose 1 unless you are certain of the assumptions you want to make regarding the length of a month and a year. Enjoy!
