☰

Excel公式會根據2個儲存格計算日期以及如何複製到其他儲存格？

tag-icon • tag-icon microsoft-excel worksheet-function date
Excel公式會根據2個儲存格計算日期以及如何複製到其他儲存格？

這裡是 Excel 新手用戶。這是我的電子表格的簡化版本：

start date  end date    eday    duration
                        -21     4
                         12     0

begindate = "03/03/2020"

我想建立公式來根據 eday 和持續時間列以及 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

開始日期03/03/2020將始終相同。

我能夠使公式正常工作，但是當我使用填充手柄時，它無法正確複製公式。

請幫新手解決以下問題：

  1. 儲存開始日期“03/03/2020”的最佳位置在哪裡 - 單獨的工作表？硬編碼它？其他？

  2. 開始日期和結束日期的公式是什麼？

  3. 如何將公式複製到該列的所有行中

非常感謝。

答案1

關於 Excel 中的日期和時間，我首先要記住的是，它們實際上只是數字，使用數字格式將其格式化為看起來像日期/時間。今天的日期（2019 年 9 月 7 日）是 43715，目前時間（12:20AM）是 0.14302083...

有一些工作表函數可以為您獲取幻數（基本上是自 1900 年 1 月 1 日以來的天數），例如DATE()、TODAY()或NOW()。

為此，假設您的一天列是C並且您的資料從行開始2，您可以輸入您的開始日期A2as=DATE(2020,3,3)+$C2和 your中的公式結束日期中的公式B2為=$A2+$D2.這是最簡單的方法。

請注意，我使用了絕對列引用 ( $C、$A、$D) 來強制 Excel 始終在列C、A和中查找D，無論我在何處複製/貼上或填充，並且我使用了相對行引用 ( 2)，這樣，如果我複製/貼上或向下填充，行號將作為填充的一部分而改變（即在 row 上3，公式將顯示為=$A3+$D3）。有時你想要這個，有時你不需要，所以你應該嘗試一下$，$看看它的作用。

我懷疑你最初把你的開始日期儲存格中的值，但在公式中使用了相對引用，並且當您填寫時，您的相對引用開始日期改變了。如果我是對的，那麼你需要一個絕對的參考開始日期儲存格，例如，如果它位於列Jrow中1，則您的公式應為$J$1，而不是J1。

當您變得更加自信時，您可以使用更複雜的功能來使其更具彈性或可配置性。例如：

  • 您可以使用姓名經理BeginDate透過建立一個名為 的新名稱並將其值設為 來儲存固定的恆定日期=DATE(2020,3,3)。現在您可以將開始日期公式更新為A2be =BeginDate+$C2，並且該固定值將在任何地方使用。

  • 您可以將四列範圍的格式設定為有標題的表格，這將允許您使用這些標題名稱而不是列引用。如果將範圍格式化為表格，則在第一個資料行中開始日期A2您可以在列（以前是）=BeginDate+[eday]中輸入 和結束日期列（曾經是B2）您可以輸入=[start date]+[duration]。因為它是一個表格，所以隨著表格的增長，兩個公式都應該自動填充。現在應用一個簡單的數字格式短日期給你的開始日期和結束日期列。

  • 當您沒有輸入任何值時，您可以停止顯示計算日期，eday或duration透過變更公式開始日期到更複雜的：

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

    ……並改變公式結束日期到：

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

相關內容