在Excel中计算两个日期之间的月数 [英] Counting Number of Months between Two Dates in Excel

问题描述

因此，我试图简单地计算出一个较早的日期与今天之间的月数，该日期将在B：B列中；一旦计算了月数，然后将结果乘以28，然后加回到原始日期。请注意以下要求： Result> = Today ，因此基本上，如果结果小于今天，则需要再增加28天。我制定的当前公式只有在日期是当年的情况下才有效（到目前为止，我还不确定100％是否可以使用此公式。）

So I am attempting to simply count the number of months bewtween an earlier date and today, which will be in the B:B column; once the number of months have been counted, the result is then multiplied by 28, then added back to the original date. Note the requirement: Result >= Today, so basically if the result is less than today it needs to add another 28 days. The current formula I made only works if the dates are in the current year (and I am not 100% sure if this formula works, it appears to so far though.)

这是我已经失效的公式，但是也许有人可以从我上面的评论和下面的公式中获得我在这里想要实现的目标的大致思路：

Here is my defunct formula, but maybe someone can get a general idea from my above comments and the below formula of what I am attempting to achieve here:

= IF（B89 =，，IF（I89 = X， LEG，IFERROR（IF（（MONTH（TODAY（）-B89）* 28）+ B89< TODAY（），（（ MONTH（TODAY（）-B89）* 28）+ B89 + 28，（MONTH（TODAY（）-B89）* 28）+ B89）， Future）））

在此先感谢您的帮助！

注意：我只想指出，在I89中对I89的引用微不足道以上。我只是不想删除它，以防万一我删除了错误的括号或其他错字，所以我决定留在那儿。因此，基本上，您不必担心前两个 IF语句，也不必担心IFERROR，除非您只是想这么做！

Note: I just want to point out that the reference to I89 is insignificant in the above. I just didn't want to remove it in case I deleted the wrong parenthesis or some other typo, so I decided to leave in there. So basically you would not need to necessarily worry about the first two "IF" statements, nor the IFERROR, unless you just wanted to!

2ND编辑：好的，我决定剥离公式，原始帖子的公式在上面，剥离的版本在下面：

2ND Okay I decided to strip down formula, original post's formula is above, the stripped version below:

IF（（MONTH（TODAY（）-B89）* 28） + B89< TODAY（），（MONTH（TODAY（）-B89）* 28）+ B89 + 28，（MONTH（TODAY（）-B89）* 28）+ B89）

推荐答案

您不应为此目的使用 MONTH（），因为这会导致错误在某些情况下会产生结果，并且肯定是在 B89 日期是另一年的情况下。

You should not use MONTH() for this purpose as this will lead to wrong results in some cases, and certainly when the B89 date is in another year.

相反，请查看您有多少天距 B89 以来28天的最后一个倍数过去，然后返回到该日期（减去），然后再添加28天：

Instead see how many days you are past the last multiple of 28 days since B89, and go back to that date (by subtracting), and then add another 28 to it:

=TODAY() + 28 - MOD((TODAY()-B89), 28)

最早可以提供的日期是明天。如果今天应该是该公式的可接受结果，则将 TODAY（）替换为 TODAY（）-1 ，其中结果如下：

The earliest date this can give is the date of tomorrow. If today should be an acceptable outcome of the formula, then replace TODAY() with TODAY()-1, which results in this formula:

=TODAY() + 27 - MOD((TODAY()-1-B89), 28)

这篇关于在Excel中计算两个日期之间的月数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！