Excel 公式根据 2 个单元格计算日期以及如何复制到其他单元格?

Excel 公式根据 2 个单元格计算日期以及如何复制到其他单元格?

我是 Excel 新手。这是我的电子表格的简化版本:

start date  end date    eday    duration
                        -21     4
                         12     0

begindate = "03/03/2020"

我想创建公式来根据 eday 和 duration 列以及 begindate 中的值来计算开始日期和结束日期:

start date = begindate + eday
end date = start date + duration

因此,对于上述示例,如果我应用公式,我会期望得到以下结果:

start date  end date    eday    duration
02/11/2020  02/11/2020  -21     4
03/15/2020  03/19/2020   12     0

begindate03/03/2020将始终相同。

我能够使公式起作用,但是当我使用填充柄时,它无法正确复制公式。

请帮助新手解决这些问题:

  1. 存储开始日期“03/03/2020”的最佳位置在哪里 - 单独的工作表?硬编码吗?其他?

  2. 开始日期和结束日期的公式是什么?

  3. 如何将公式复制到列中的所有行中

非常感谢。

答案1

关于 Excel 中的日期和时间,我首先要记住的是,它们实际上只是数字,使用数字格式将其格式化为看起来像日期/时间。今天的日期(2019 年 9 月 7 日)是 43715,当前时间(凌晨 12:20)是 0.14302083...

有一些工作表函数可以为您获取神奇数字(基本上是自 1900 年 1 月 1 日以来的天数),例如DATE()或。TODAY()NOW()

为此,假设你的埃迪列是C,你的数据从行开始2,你可以输入你的开始日期公式在A2=DATE(2020,3,3)+$C2你的结束日期公式为B2=$A2+$D2这是最简单的方法。

请注意,我使用了绝对列引用($C$A$D)来强制 Excel 始终在 、 和 列中查找CAD不管我在哪里复制/粘贴或填充,并且我使用了相对行引用(2),这样,如果我复制/粘贴或向下填充,行号将作为填充的一部分而更改(即在行 上3,公式将显示=$A3+$D3)。有时您想要这样,有时您不需要,因此您应该尝试使用$和 来$查看它的作用。

我怀疑你最初把你的开始日期单元格中的值,但在公式中使用了相对引用,并且当您向下填充时,您的相对引用开始日期改变。如果我是对的,那么你需要一个绝对的参考来开始日期单元格,例如,如果它位于列J行中1,则您的公式应该读取$J$1,而不是J1

随着你越来越自信,你可以使用更复杂的功能来提高其弹性或可配置性。例如:

  • 您可以使用姓名经理BeginDate通过创建一个名为 的新名称并将其值设置为 来存储固定的常量日期=DATE(2020,3,3)。现在,您可以将 中的开始日期公式更新A2=BeginDate+$C2,并且该固定值将在任何地方使用。

  • 您可以将四列范围格式化为带标题的表格,这将允许您使用这些标题名称而不是列引用。如果您将范围格式化为表格,则在第一个数据行中开始日期列(这是A2)你可以输入=BeginDate+[eday]和在你的结束日期列(原为B2)中,您可以输入=[start date]+[duration]。由于这是一张表格,因此随着表格的增长,两个公式都会自动填充。现在应用一个简单的数字格式短日期你的开始日期结束日期列。

  • 您可以停止表格显示计算日期,当您没有输入任何值时,eday或者duration通过更改公式开始日期更复杂的是:

    =IF([eday]="", "", BeginDate+[eday])
    

    ...并改变公式结束日期到:

    =IF(OR([start date]="",[duration]=""), "", [start date]+[duration])
    

相关内容