☰

如何在 Excel 中计算下一个 N 年周年纪念日

tag-icon • tag-icon microsoft-excel
如何在 Excel 中计算下一个 N 年周年纪念日

我发现这个教程在 ExcelJet 上计算下一个周年纪念日。

我该如何修改它来计算下一个多年期的年份。例如，我有一些日期项不会发生，但每 3、5、8 或 10 年发生一次。

在此处输入图片描述

答案1

如果该项目从“日期”开始每隔几年发生一次，我认为需要添加条件。

例如，多年期是3，你可以尝试下面的公式。 =IF(MOD(DATEDIF(A2,B2,"y"),3)=0,EDATE(A2,(DATEDIF(A2,B2,"y")+3)*12),EDATE(A2,(DATEDIF(A2,B2,"y")+3-(MOD(DATEDIF(A2,B2,"y"),3)))*12))

在此处输入图片描述

对于其他多年期，您可以用多年期数字替换公式中的“3”。

5年：=IF(MOD(DATEDIF(A2,B2,"y"),5)=0,EDATE(A2,(DATEDIF(A2,B2,"y")+5)*12),EDATE(A2,(DATEDIF(A2,B2,"y")+5-(MOD(DATEDIF(A2,B2,"y"),5)))*12))

8年：=IF(MOD(DATEDIF(A2,B2,"y"),8)=0,EDATE(A2,(DATEDIF(A2,B2,"y")+8)*12),EDATE(A2,(DATEDIF(A2,B2,"y")+8-(MOD(DATEDIF(A2,B2,"y"),8)))*12))

10年：=IF(MOD(DATEDIF(A2,B2,"y"),10)=0,EDATE(A2,(DATEDIF(A2,B2,"y")+10)*12),EDATE(A2,(DATEDIF(A2,B2,"y")+10-(MOD(DATEDIF(A2,B2,"y"),10)))*12))

在此处输入图片描述

答案2

公式中的+1加一年。要得到问题中提到的其他日期，请将+1其改为+3、+5和。+8+10

第 1、3、5、8 和 10 年的公式为：

=LET(x,DATEDIF($A6,TODAY(),"y"),EDATE($A6,(x+1)*12))
=LET(x,DATEDIF($A6,TODAY(),"y"),IF(MOD(x,3)=0,EDATE($A6,(x+3)*12),EDATE($A6,(x+3-MOD(x,3))*12)))
=LET(x,DATEDIF($A6,TODAY(),"y"),IF(MOD(x,5)=0,EDATE($A6,(x+5)*12),EDATE($A6,(x+5-MOD(x,5))*12)))
=LET(x,DATEDIF($A6,TODAY(),"y"),IF(MOD(x,8)=0,EDATE($A6,(x+8)*12),EDATE($A6,(x+3-MOD(x,8))*12)))
=LET(x,DATEDIF($A6,TODAY(),"y"),IF(MOD(x,10)=0,EDATE($A6,(x+10)*12),EDATE($A6,(x+10-MOD(x,10))*12)))

根据 OP 中的图片示例以及将“截至”列视为今天的评论，以下是相应的日期：

Excel 中下一个 N 年周年纪念结果

答案3

我想建议一个动态公式来解决这个问题：

在此处输入图片描述

  • 单元格 J63 中的公式：

    =EDATE($H63,(DATEDIF($H63,$I63,"y")+VALUE(LEFT(J$62,2)))*12)

::已編輯::

让我解释一下这个公式的工作原理：

  • 只需打破公式并首先获得=DATEDIF(H63,I63,"y")回报42。
  • 现在获取=VALUE(LEFT(J$62,2))回报1。
  • 现在将两者结合起来。

=(DATEDIF($H63,$I63,"y")+VALUE(LEFT(J$62,2)))*12,,

回报516，是月因为结果乘以了12,, (42+1)*12。

  • 现在这样做=EDATE(H63,516)，，返回14-03-2018。

  • 您可以通过516 在单元格 H63 中的日期中添加月份来交叉检查这一点：

    =DATE(YEAR(H63),MONTH(H63)+516,DAY(H63))

  • 或者你可以尝试这个：

     =DATE(YEAR(H63),MONTH(H63)+(DATEDIF($H63,$I63,"y")+VALUE(LEFT(J$62,2)))*12,DAY(H63))

你进入14-03-2018单元格 J63，然后下一步。

注意：

  • 在上面的公式VALUE(LEFT(J$62,2)))中年数来自文本的数字为 1、3、5、8 和 10，并且使公式动态化而不是硬编码。
  • 否则，您必须像 J63 中的公式一样修改每年的公式 =EDATE($H63,(DATEDIF($H63,$I63,"y")+3)*12)。
  • 您可以根据需要调整公式中的单元格引用。

相关内容