☰

如何计算在某个日期范围内 31 日出现的次数

tag-icon • tag-icon microsoft-excel
如何计算在某个日期范围内 31 日出现的次数

用于计算利息。例如，任何按 30/365 计算的事物都会每天累积利息，但每月的 31 号除外。我想找出一种方法来判断两个日期之间 31 号出现的频率 - 例如，从 2017 年 3 月 15 日到今天，有 2 个 31 号 - 3 月 31 日和 5 月 31 日。除了在公式中列出每个月之外，有没有其他好的方法可以在 Excel 中解决这个问题？

答案1

你可以使用这个：

=SUMPRODUCT(--(DAY(EOMONTH(B1,ROW(INDIRECT("1:" & (MONTH(TODAY())-MONTH(B1))+12*(YEAR(TODAY())-YEAR(B1))))-1))=31))

它的作用是返回所需日期与今天之间每个月的最后一天。如果等于 31，则将其计入。SUMPRODUCT 将每个真值计为 1，将假值计为 0。

因此，它计算以 31 结尾的月份。

![在此处输入图片描述

答案2

出于兴趣，即使有一个可以接受的答案，这也会起作用，并且会更短一些：

=SUMPRODUCT(N(DAY(ROW(INDIRECT(StartDate &":"&EndDate)))=31))

它创建一个包含从开始到结束的所有日期的数组，并计算天数 = 31。

这种方法会遇到可怕的25 Nov 4770问题，除非 Excel 已经增加了行数 :-)

答案3

你可以利用每年有 7 个 31 日这一事实。开始日期B1和结束日期分别为B2：

=(YEAR(B2)-YEAR(B1)+1)*7-INDEX({0,1,1,2,2,3,3,4,5,5,6,6},MONTH(B1))-INDEX({7,6,6,5,5,4,4,3,2,2,1,1},MONTH(B2))+(DAY(B2)=31)

理由：

  • 计算开始日期和结束日期的年份差值（不考虑月份）。将差值加上 1 乘以 7。这将提供 31 个月份的基数超额计数，我们将从中减去该数。
  • 接下来，查找与开始日期在同一日历年中的前 31 个日期的数量。这由数组处理，数组为每个月提供此值。从总数中减去此值。
  • 然后，对结束日期进行类似操作，但查找与结束日期相同的日历年中剩余的 31 个数字。这个值也被减去。
  • 最后，纠正结束日期为某月 31 日的特殊情况。在这种情况下，将总数加 1。

例子：

开始：9/6/2013
结束：12/31/2016

  • 年份差异 = 2016 - 2013 = 3 --> 将 (3 + 1) * 7 = 28 添加到 31 的数字上。
  • 查找开始日期日历年份之前的 31 号：9 月有 5 个之前的 31 号。--> 从 31 号的数量中减去 5：28 - 5 = 23
  • 查找结束日期日历年的剩余 31 个 31 日：12 月日期剩余 1 个 31 日。--> 从 31 日的数量中减去 1：23 - 1 = 22
  • 检查 31 日的结束日期：结束日期是 31 日，因此将 31 日的数字加 1：22 + 1 = 23。

相关内容