☰

计算特定年份范围内的天数

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
计算特定年份范围内的天数

样本数据：

样本数据

嗨，我一直在努力寻找正确的 Excel 公式来返回日期范围内特定年份的天数。2010 年曾有一篇类似的帖子，但对我来说没用。

答案1

我看到了一个不需要 VBA 的简单解决方案（对于简单的解决方案来说，这是一个很长的答案，因为它包含了涵盖问题中模棱两可的几件事的变体）。

在此处输入图片描述

请注意，此答案包含针对不同用例的多个版本的公式。图片中显示的公式只是一个，反映的是“持续时间”而不是天数，如问题示例中所示。

“天数”定义

有几种解释方法。如果某件事在今天中午开始，在明天中午结束，那么你可以认为它持续了一天。如果你对它发生的日历天数感兴趣，那么你可以认为它持续了两天。我将展示这两种情况，你可以应用与你相关的一种。

开始和结束只是日期与日期/时间

如果开始和结束范围只是日期，则范围将以整天为单位存储，因此您可以使用简单的算术来计算持续时间。如果它们实际上存储为日期/时间，而您只显示日期部分，则隐藏的时间信息可能会导致意外的舍入错误。我将展示针对这两种情况的解决方案。

解决方案：持续时间基础

因此，任务是确定范围的任何部分是否落在给定的年份内，然后计算落在给定年份内的部分的持续时间。此公式使用年份列标题，因此需要比上次完成日期晚一年的列。

如果范围开始和结束是只是日期目标是“持续时间”，D2 中的公式（拖动以填充矩阵）：

=IF(OR($A2>=E$1,$B2<D$1),0,MIN($B2,E$1)-MAX($A2,D$1))

如果范围 Start 和 Finish 包含约会时间信息，目标是“持续时间”，D2 中的公式：

=IF(OR($A2>=E$1,$B2<D$1),0,DATEDIF(MAX($A2,D$1),MIN($B2,E$1),"D"))

内置的DATEDIF函数可以给出日期之间的差异（忽略时间信息），并且您可以指定显示的单位。

解释

在这两个公式中，IF 测试检查开始日期是否在当前年份结束之后或完成日期是否在当前年份之前。如果是，则填充零。

否则，它将计算日期差。它使用较晚的年份开始时间（延续上一年的范围）或范围开始时间（在一年中的某个时间开始），以及较早的年份结束时间（在一年中结束）或下一年的开始时间（范围延续到下一年）。

解决方案：按天计数

如果您的目标是将范围内的每一天都算作一整天，我们需要在该范围的最后一年的条目上添加一天。一个简单的方法是使用布尔值（true=1，false=0）来测试完成年份是否等于“当前”年份：(YEAR($B2)=YEAR(D$1))，可以将其添加到上述任一公式中。例如，使用第一个公式：

=IF(OR($A2>=E$1,$B2<D$1),0,MIN($B2,E$1)-MAX($A2,D$1)+(YEAR($B2)=YEAR(D$1)))

答案2

公式解决方案非常简单：

工作表截图

在 中输入以下公式D2，然后按 ctrl-enter/复制-粘贴/向下填充到 中D2:K4：

=
IF(AND(YEAR(D$1)=YEAR($A2),YEAR(D$1)=YEAR($B2)),$B2-$A2+1,
IF(YEAR(D$1)=YEAR($A2),EDATE(D$1,12)-$A2,
IF(YEAR(D$1)=YEAR($B2),$B2-D$1+1,
IF(AND(YEAR(D$1)>YEAR($A2),YEAR(D$1)<YEAR($B2)),EDATE(D$1,12)-D$1,
0
))))

解决这个问题的关键是把它分成五个独立的案例：

  1. 开始和结束年份与相关年份相符
  2. 仅开始年份匹配
  3. 仅匹配完成年份
  4. 所讨论的年份介于开始年份和结束年份之间
  5. 所讨论的年份不在开始和结束年份之内

编辑：

看到 fixer1234 的解决方案让我开始疑惑为什么我放弃了最初使用MIN()&MAX()来解决问题的想法——显然没有什么好的理由。

这是我最初应该提出的解决方案：

=(EDATE(D$1,12)>=$A2)*(D$1<=$B2)*(MIN($B2,EDATE(D$1,12))-MAX($A2,D$1)+(YEAR(D$1)=YEAR($B2)))

答案3

尝试以下用户定义函数：

Public Function DateCounter(StartDate As Date, EndDate As Date, yeer As Long) As Long
    Dim d As Date
    DateCounter = 0

    For d = StartDate To EndDate
        If Year(d) = yeer Then DateCounter = DateCounter + 1
    Next d
End Function

第一个参数是开始日期，第二个参数是结束日期，第三个参数是年份。例如：

在此处输入图片描述

因此，（包括）2016 年 11 月 20 日至 2017 年 3 月 15 日，2016 年秋季为 42。

用户定义函数 (UDF) 非常容易安装并使用：

  1. ALT-F11 打开 VBE 窗口
  2. ALT-I ALT-M 打开新模块
  3. 粘贴内容并关闭 VBE 窗口

如果您保存工作簿，UDF 将随之保存。如果您使用的是 2003 之后的 Excel 版本，则必须将文件保存为 .xlsm 而不是 .xlsx

到消除UDF：

  1. 调出如上所示的 VBE 窗口
  2. 清除代码
  3. 关闭 VBE 窗口

到使用Excel 中的 UDF：

=myfunction(A1)

要了解有关宏的更多信息，请参阅：

http://www.mvps.org/dmcritchie/excel/getstarted.htm

和

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

有关 UDF 的详细信息，请参阅：

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

必须启用宏才能使其工作！

相关内容