This page looks plain and unstyled because you're using a non-standard compliant browser. To see it in its best form, please visit upgrade to a browser that supports web standards. It's free and painless.

阿不辣的酸甜苦辣 會員登入 會員註冊

« 上一篇 | 下一篇 »

最近剛好在做一些  Excel 的 Case, 搜尋到以下資料與大家分享 :

原文來自微軟訓練中心連結 http://office.microsoft.com/training/training.aspx?AssetID=RP061771091028&CTT=6&Origin=RC061771061028

如何使用 Microsoft® Excel 公式來推算日期

Excel 日期的模樣

 
圖說文字 1 您認為的日期模樣。
圖說文字 2 Excel 日期的模樣。

您和 Excel 對於日期的認定,是有所不同的。對您而言, 8/22/2005代表月、日和年。對 Excel 而言,8/22/2005是序列值 38586。在這個單元中,您將學習到如何以 Excel 的日期觀點來計算日期,以及如何以 Excel能夠辨識的日期格式來輸入。在計算之前先了解日期,可幫您節省之後需花費的時間。


行事曆從 1900開始

  image
Excel 使用序列值來儲存日期。

Excel 使用序列值來儲存日期,從 1 開始,代表 19001 1 日。也是 Excel 行事曆的開始日期。以後每過一天,序列值就加 1。例如,19901 2 日儲存為 2

Excel 將日期儲存為序列值,才能計算日期。例如,若要尋找兩個日期之間的天數, Excel 只要將二個序列值相減即可。

附註 Excel 公式不接受 19901 1 日之前的日期。

 

  Excel如何辨識日期

image
1.[類別]清單中選取 [日期]
2.[類型]清單中選取日期格式。

 

使用斜線或連字號,區隔 Excel 的日期部分。 Excel 將這種格式的資訊視為日期,並且儲存為序列值:8/22/2005 22-Aug-2005 都儲存為序列號 38586。未來 Excel 可將 August 22, 2005辨識為日期。

Excel 可以將您鍵入的日期格式,變更為預設格式。例如, Excel 可以將您鍵入的 August 22, 2005,變更為 22-Aug-05 或別的預設格式。任何格式的日期,它的序列值一定是 38586。除非 Excel無法辨識您使用的格式。

例如,如果您鍵入 August 22 20058,22,05Excel 會將它儲存為純文字,而非 38586

如需斜線或連字號在日期格式中的其他用法,請開啟圖片中顯示的 [儲存格格式] 對話方塊。

 

使用公式計算日期

函數名稱

用來尋找

NETWORKDAYS

兩個日期之間的工作天數淨值

WORKDAY

另一個日期的指定工作天數之後的日期

DATE

另一個日期的指定年、月或日數之後的日期

TODAY

目前的日期

 

Excel 中是使用公式來進行運算的。在這個單元中,您將學習到如何使用簡單的公式來尋找兩個日期之間的天數,並了解鍵入 Excel 能夠辨識的日期是很重要的。您也將學習到如何使用預先寫好的公式,也就是函數,來執行其他的日期計算,例如,看看到今天為止,您的年齡換算成天數的結果。在單元結尾的練習活動中,您將有機會使用所學到的公式。

 

尋找兩個日期之間的天數

image 
1.      工作表公式
2.      公式結果
3.      資料編輯列公式

假設今天是 2004 6 9 日,您從 2004 8 21 日開始休假。您想知道還要等幾天才休假。您可以在空白儲存格 (本例的儲存格 B5) 中鍵入簡單的公式來計算:

=A3-A2

兩個日期之間有 73 天。您可以在公式中使用日期,因為 Excel 能夠辨識儲存格 A2 A3 的日期格式值,並且將它儲存為序列值。Excel 8/21/2004 的序列值減 6/9/2004 的序列值:38220 38147 等於 73

您鍵入的等號告知 Excel 這是公式。減號運算子 (-) 指示 Excel 將二數相減。A3 A2 是儲存格值的儲存格參照。

如果日後不知道這個結果是怎麼來的,您只要選取所使用的儲存格,工作表頂端附近的資料編輯列 會顯示公式。您也可以按兩下儲存格,顯示儲存格公式。

附註 公式先以日期格式顯示結果 73,因為 A2 A3 值是日期格式。在單元結尾的練習活動中,您將了解如何迅速重設結果格式,以讓它以數字的型式出現。

尋找兩個日期之間的工作天數

image 
1.工作表公式
2.公式結果
3.資料編輯列公式

您不能用兩個日期相減來尋找兩個日期之間的工作天數 (星期一到五)。您應該使用預先寫好的公式(稱作 NETWORKDAYS 函數) 來計算兩個日期之間的工作天數淨值。

仍然假設今天是 2004 6 9 日,您從 2004 8 21 日開始休假。您想知道還要等幾個工作天才休假。您可以在空白儲存格中鍵入公式:

=NETWORKDAYS(A2,A3)

要等 53 個工作天才休假。儲存格 A2 A3 的值是引數,這項資訊告知 NETWORKDAYS 的計算內容。函數中的引數以括弧來區隔,括弧中的引數之間以逗點來區隔。

尋找指定工作天數之後的日期

image 
1.工作表公式
2.公式結果
3.資料編輯列公式

假設您要尋找某個日期的指定工作天數 (星期一到五) 之前或後的日期,例如,發票或專案的結束日期。很簡單:您可以使用 WORKDAY 函數。

假設您從 12/31/2004 開始,必須在 80 個工作天內完成專案。您要找出專案的結束日期。首先,您必須如圖片所示,在儲存格 A2 A3 輸入這些數值。接著輸入可能影響計算的國定假日,一個儲存格輸入一個國定假日。然後在空白儲存格中鍵入公式:

=WORKDAY(A2,A3,A4:A6)

專案必須在 4/26/05 完成。Excel 12/31/2004 (儲存格 A2) 加上 80 (儲存格 A3),並且自動排除引數 A4:A6 (冒號代表儲存格範圍) 列出的這段期間的週末和國定假日。引數之間以逗點來區隔,函數中的引數以括弧來區隔。


尋找指定月數之後的日期

image 
1.工作表公式
2.公式結果
3.資料編輯列公式

假設您從 6/9/2007 開始,必須在 25 個月內完成專案。您要找出專案的結束日期。您可以使用 DATE函數來尋找。DATE 函數有三個引數:年、月、日。先在儲存格 B2 輸入 25,然後在儲存格 A4 鍵入這個公式:

=DATE(2007,6+B2,9)

2007 是年引數,6 加儲存格 B2 值是月引數,9 是日引數。請以逗點來區隔引數,以括弧圍住所有引數。

專案的結束日期是 7/9/2009。您鍵入的是儲存格參照 (B2) 而非儲存格值 (25),如果數值變更,Excel會自動更新結果。例如,如果期限從 25 個月縮短為 23 個月,您只要將儲存格 B2 的值 25 變更為 23,結束日期就會自動修訂為 5/9/2009,不必重新鍵入公式。

尋找指定年數、月數和日數之後的日期

image

1.工作表公式
2.公式結果
3.資料編輯列公式

假設您從 2007 6 9 日開始,必須在 1 7 個月又 5 天內完成專案。您要找出專案的結束日期。您同樣可以使用 DATE 函數,也就是如圖片所示,輸入這些儲存格值,並且在儲存格 A6 鍵入這個公式:

=DATE(2007+B2,6+B3,9+B4)

2007 加儲存格 B2 值是年引數,6 加儲存格 B3 值是月引數,9 加儲存格 B4 值是日引數。

專案的結束日期是 2009 1 14 日。您在上一個公式中鍵入的是儲存格參照 B2B3 B4 而非儲存格值,如果數值變更,Excel 會自動更新結果。如果期限縮短或延長,您只要變更儲存格 B2B3 B4 的值,Excel 會自動修訂結果。

到今天為止,您的年齡換算成天數的結果

image
TODAY 函數沒有引數。

TODAY Excel 少數幾個不要任何引數的特殊函數,但它需要左右括弧:=TODAY()

TODAY 插入的目前日期,會在每次開啟或重新計算活頁簿時更新。

純粹出於好玩,您可以使用 TODAY 函數,來看看到今天為止,您的年齡換算成天數的結果。您將在在練習活動中看看計算的方法。

附註 這個單元中的函數只是 Excel 眾多函數的一小部分。「尋找函數和輸入引數」會說明如何尋找各種計算用的其他函數。

原文來自微軟訓練中心

原文連結 http://office.microsoft.com/training/training.aspx?AssetID=RP061771091028&CTT=6&Origin=RC061771061028

  1. Re: 如何使用 Microsoft® Excel 公式來推算日期 [回覆]

    請問:

    圖示在哪裡呢? 我在您網頁中找到吔, 謝謝!!!

    Jo 回應於 28 十月, 2009 18:33

      Andy Yu Re: Re: 如何使用 Microsoft® Excel 公式來推算日期 [回覆]

      謝謝告知!! 已補上

      Andy Yu 回應於 02 十一月, 2009 11:02