☰

计算两个不同日期之间每年的天数 Excel

tag-icon • tag-icon microsoft-excel
计算两个不同日期之间每年的天数 Excel

我有一张 Excel 数据表，其中包含大约 200 行，每行都有一个开始日期和结束日期。我需要计算每年日期之间的差异。假设开始日期为 2017 年 3 月 3 日，结束日期为 2019 年 3 月 2 日，我需要计算这两个日期之间在 2017 年、2018 年和 2019 年的天数。

答案1

此公式应该可以一直有效到 4770 年 11 月 25 日，到那时 Excel 可能已被其他公式所取代。

=SUMPRODUCT(--(YEAR(ROW(INDIRECT(StartDt &":"&endDt)))=C$1))

其中 C1 包含相关年份。

例如：

在此处输入图片描述

但是，您写道您想知道“日期之间的差异”。这种措辞通常表示您不想计算第一个日期。该公式会计算所有日期。

如果您确实想要“差异”而不是从开始到结束的天数，那么请在公式中的开始日期上加一：

=SUMPRODUCT(--(YEAR(ROW(INDIRECT(StartDt+1 &":"&endDt)))=C$1))

编辑： （由@fixer1234 挑衅）要理解这是如何运作的，你需要理解

  • 日期在 Excel 中存储为序列号，通常1-jan-1900等于1。
  • ROW(INDIRECT(n:m))是一种返回等于n和所表示的行号的数字数组的方法m
  • 然后，函数YEAR返回每个值的等效年份，并与相关列顶部的年份进行比较，生成一个数组TRUE;FALSE。
  • 因为在 Excel 中，=--TRUE将有效返回位于列顶部的年份的所有日期。1SUMPRODUCTSUM
  • 我提到的日期限制是因为2^20Excel 中只有行。可以通过对ROW(INDIRECT(…构造中的日期应用偏移量，然后在之后添加偏移量（如果涉及的日期晚于 11/25/4770）来克服此限制。

答案2

如果我理解正确，假设您想要计算 2019 年两个日期之间的天数。如果开始日期 = 30/12/2019 且结束日期 = 03/01/2021，则公式应返回数字 2。（或 1，具体取决于您想要如何计算）。

为此，请尝试以下操作：

= Max(31/12/2018,Min(结束日期,31/12/2019)) - Min(31/12/2019,Max(开始日期,31/122018))

其中，您将有一个输入日期（31/12/2018 和 2019）的单元格引用，而不是日期。

我发现这个公式的唯一缺点是，你必须确保结束日期大于或等于开始日期。从逻辑上讲，情况应该总是如此，除非有一个日期缺失或输入错误。

希望这可以帮助。

干杯

相关内容