如果您有一个包含很多日期的Excel 表(Excel sheet)，您最终可能需要计算其中一些日期之间的差异。也许你想看看你花了多少个月还清债务，或者你花了多少天才减掉一定的体重？
在Excel(Excel)中计算日期之间的差异很容易，但可能会变得复杂，具体取决于您要如何计算某些值。例如，如果您想知道 2/5/2016 和 1/15/2016 之间的月数，答案应该是 0 还是 1？有些人可能会说 0，因为日期之间不是一个完整的月份，而其他人可能会说 1，因为它是不同的月份。
在本文中，我将向您展示如何计算两个日期之间的差异，以根据您的喜好使用不同的公式计算天数、月数和年数。
两个日期之间的天数
我们可以做的最简单的计算是获取两个日期之间的天数。计算天数的好处是实际上只有一种方法可以计算该值，因此您不必担心不同的公式会给出不同的答案。
在上面的示例中，我有两个日期存储在单元格A2 和 A3(A2 and A3)中。在右侧，您可以看到这两个日期之间的差异是 802 天。在Excel 中(Excel)，总是有多种方法可以计算相同的值，这就是我在这里所做的。让我们看一下公式：
第一个公式只是两个日期A3 – A2的简单减法。Excel知道这是一个日期，并简单地计算这两个日期之间的天数。简单(Easy)且非常直接。此外，您还可以使用DAYS函数。
=DAYS(A3, A2)
这个函数有两个参数：结束日期(end date)和开始日期(start date)。如果你在公式中切换日期，你只会得到一个负数。最后，您可以使用名为DATEDIF的函数，该函数从Lotus 1-2-3 天开始就包含在Excel中，但不是Excel中官方支持的公式。
=DATEDIF(A2, A3, "D")
当您键入公式时，您会看到Excel没有为您提供任何有关字段等的建议。幸运的是，您可以在此处查看DATEDIF 函数(DATEDIF function)的语法和所有支持的参数。
另外，如果您想计算两个日期之间的工作日数怎么办？这也很容易，因为我们有一个内置公式：
=NETWORKDAYS(startDate,endDate)
计算天数和工作日数很简单，所以现在让我们谈谈月份。
两个日期之间的月份
最棘手的计算是月数，因为您可以根据是完整月还是部分月来向上或向下舍入。现在Excel(Excel)中有一个MONTHS 函数(MONTHS function)，但它非常有限，因为它在计算差异时只会查看月份而不是年份。这意味着它仅对计算同一年两个月之间的差异有用。
既然那是毫无意义的，让我们看一些可以为我们提供正确答案的公式。微软在(Microsoft)这里(here)提供了这些，但如果你懒得访问链接，我也在下面提供了它们。
Round Up - =(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)
Round Down - =IF(DAY(LDate)>=DAY(EDate),0,-1)+(YEAR(LDate)-YEAR(EDate))
*12+MONTH(LDate)-MONTH(EDate)
现在这是两个相当长且复杂的公式，你真的不需要了解发生了什么。以下是 Excel 中的公式：
请注意，您应该在实际单元格本身中编辑向下取整公式，因为由于某些奇怪的原因，整个公式不会显示在公式栏中(formula bar)。为了在单元格本身中查看公式，请单击“公式(Formulas)”选项卡，然后单击“显示公式”(Show Formulas)。
那么我们当前示例中两个公式的最终结果是什么？让我们看看下面：
向上(Round)取整给我 27 个月，向下取整给我 26 个月，这是准确的，这取决于你如何看待它。最后，您还可以使用DATEDIF 函数(DATEDIF function)，但它只计算整月，所以在我们的例子中，它返回的答案是 26。
=DATEDIF(A2, A3, "M")
两个日期之间的年份
与月份一样，年数也可以根据您是想将 1 年计算为完整的 365 天还是计算年份的变化来计算。因此，让我们举个例子，我使用了两个不同的公式来计算年数：
一个公式使用DATEDIF，另一个使用YEAR 函数(YEAR function)。由于天数的差异仅为 802，因此DATEDIF显示 2 年，而YEAR 函数(YEAR function)显示 3 年。
同样，这是一个偏好问题，取决于您要计算的确切内容。了解这两种方法是个好主意，这样您就可以使用不同的公式解决不同的问题。
只要您注意使用的公式，就很容易准确地计算出您要查找的内容。除了我提到的那些之外，还有更多的日期功能，因此请随时在Office 支持(Office Support)网站上查看这些功能。如果您有任何问题，请随时发表评论。享受！
How to Subtract Dates in Excel
If you have an Excel sheet with a lot of dates, chances arе you will eventually need to calculate the differences between some of those dates. Maybe you want to see how many months іt took you to pay off your debt or how many days it took you to lose a certain amount of weight?
Calculating the difference between dates in Excel is easy, but can become complicated depending on how you want to calculate certain values. For example, if you wanted to know the number of months between 2/5/2016 and 1/15/2016, should the answer be 0 or 1? Some people might say 0 since it’s not a full month between the dates and others might say 1 because it’s a different month.
In this article, I’ll show you how to calculate the differences between two dates to find the number of days, months and years with different formulas, depending on your preferences.
Days Between Two Dates
The simplest calculation we can do is to get the number of days between two dates. The good thing about calculating days is that there really is only one way to calculate the value, so you don’t have to worry about different formulas giving you different answers.
In my example above, I have two dates stored in cells A2 and A3. At the right, you can see the difference between those two dates is 802 days. In Excel, there are always multiple ways to calculate the same value and that’s what I did here. Let’s take a look at the formulas:
The first formula is just a simple subtraction of the two dates, A3 – A2. Excel knows it’s a date and simply calculates the number of days between those two dates. Easy and very straight-forward. In addition, you can also use the DAYS function.
=DAYS(A3, A2)
This function takes two arguments: the end date and the start date. If you switch the dates in the formula, you’ll just get a negative number. Lastly, you can use a function called DATEDIF, which is included in Excel from Lotus 1-2-3 days, but isn’t an officially supported formula in Excel.
=DATEDIF(A2, A3, "D")
When you type the formula, you’ll see that Excel does not give you any suggestions for the fields, etc. Luckily, you can see the syntax and all supported arguments for the DATEDIF function here.
As an added bonus, what if you wanted to calculate the number of weekdays between two dates? That’s easy enough also because we have a built-in formula:
=NETWORKDAYS(startDate,endDate)
Calculating the number of days and weekdays is simple enough, so let’s talk about months now.
Months Between Two Dates
The trickiest calculation is the number of months because of how you can either round up or round down depending on whether it’s a complete month or a partial month. Now there is a MONTHS function in Excel, but it’s very limited because it will only look at the month when calculating the difference and not the year. This means it’s only useful for calculating the difference between two months in the same year.
Since that is kind of pointless, let’s look at some formulas that will get us the correct answer. Microsoft has provided these here, but if you are too lazy to visit the link, I have provided them below also.
Round Up - =(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)
Round Down - =IF(DAY(LDate)>=DAY(EDate),0,-1)+(YEAR(LDate)-YEAR(EDate))
*12+MONTH(LDate)-MONTH(EDate)
Now these are two fairly long and complicated formulas and you really don’t need to understand what’s going on. Here are the formulas in Excel:
Note the you should edit the round down formula in the actual cell itself because for some odd reason the entire formula does not show up in the formula bar. In order to see the formula in the cell itself, click on the Formulas tab and then click Show Formulas.
So what’s the final result of the two formulas on our current example? Let’s see below:
Round up gives me 27 months and round down gives me 26 months, which is accurate, depending on how you want to look at it. Lastly, you can also use the DATEDIF function, but it only calculates full months, so in our case the answer it returns is 26.
=DATEDIF(A2, A3, "M")
Years Between Two Dates
As with months, years can also be calculated depending on whether you want to count 1 year as a full 365 days or if a change in the year counts. So let’s take our example where I have used two different formulas for calculating the number of years:
One formula uses DATEDIF and the other uses the YEAR function. Since the difference in the number of days is only 802, DATEDIF shows 2 years whereas the YEAR function shows 3 years.
Again, this is a matter of preference and depends on what exactly you are trying to calculate. It’s a good idea to know both methods so that you can attack different problems with different formulas.
As long as you are careful with which formulas you use, it’s fairly easy to calculate exactly what you are looking for. There are also a lot more date functions outside of the ones I mentioned, so feel free to check those out also on the Office Support site. If you have any questions, feel free to comment. Enjoy!
