文章編號: 214094 - 檢視此文章適用的產品。
?

關於 Microsoft Office Excel 2007,請造訪 Office 線上 Excel 2007 說明和作法網頁
全部展開 | 全部摺疊

在此頁中

結論

本文將逐步告訴您,如何使用 Microsoft Excel 的內建日期和時間功能來執行複雜的日期和時間計算。

Excel 將所有日期儲存為整數,並將所有時間儲存為十進位小數。使用這種系統,Excel 可以像其他數字一樣的加、減或比較日期和時間,而所有日期也是以這個系統來操作。

在這個系統中,序列值 1 代表 1/1/1900 12:00:00 a.m。時間儲存為 .0 到 .99999 之間的十進位小數,其中 .0 是 00:00:00 而 .99999 是 23:59:59。結合日期整數和時間小數,就能建立具有小數和整數部份的數字。例如,數字 32331.06 代表日期和時間 7/7/1988年 1:26:24 a.m。

為了協助您執行複雜的日期和時間計算,Excel 包含許多內建的日期和時間函數。

TODAY() 函數和 NOW() 函數

TODAY 函數會根據您的系統時鐘傳回今天的日期序列值,不包含時間。NOW 函數則傳回包含時間的今天日期序列值。

如何根據序列值排序日期

在 Excel 中是根據日期的序列值來排序日期,而非根據顯示的數字。因此,當您在 Excel 中排序日期,可能不會收到預期的結果。

例如,如果您排序以 mmmm 日期格式顯示的一系列日期 (因此只顯示月份),並不會按照字母順序排序月份。而是根據日期的基本日期序列值來排序。

如何根據序列值比較日期

因為日期和時間的比較中也使用序列值,實際結果可能與您預期的不同 (根據顯示的值)。

例如,使用 NOW 函數來比較日期和目前日期,如以下公式所示:
=IF(NOW()=DATEVALUE("10/1/92"),TRUE,FALSE)
即使目前的日期是 10/1/92,此公式仍會傳回 FALSE;只有在日期是 10/1/92 12:00:00 a.m 時才會傳回 TRUE。如果您要在公式中比較兩個

日期,但不想在結果中加入時間,則可改用 TODAY 函數:
=IF(TODAY()=DATEVALUE("10/1/92"),TRUE,FALSE)

如何使用日期公式

如何尋找今天和未來日期之間的日數

若要尋找今天和未來某個日期之間的日數,請使用下列公式
="mm/dd/yy"-NOW()
其中 "mm/dd/yy" 是未來的日期。對包含公式的儲存格使用通用格式。

如何尋找兩個日期之間的日數、月數和年數

若要計算兩個日期之間的日數、月數和年數 (開始和結束日期分別在 A1 和 A2 儲存格中輸入),請依照下列步驟執行:

1. 建立新的活頁簿。
2. 按一下 [空白活頁簿]
3. 在活頁簿中輸入下列資料:

A1:03/25/94A2:05/01/98
在儲存格 D1 中輸入下列公式:
 =YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<MONTH(A1),AND(MONTH(A2)=MONTH(A1), DAY(A2)<DAY(A1))),1,0)&" years, "&MONTH(A2)-MONTH(A1)+IF(AND(MONTH(A2) <=MONTH(A1),DAY(A2)<DAY(A1)),11,IF(AND(MONTH(A2)<MONTH(A1),DAY(A2) >=DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),DAY(A2)<DAY(A1)),-1)))&" months, "&A2-DATE(YEAR(A2),MONTH(A2)-IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&" days"

注意:如果您複製並貼上此公式,請確定沒有分行符號。如果有分行符號,公式將無法運作。

4. 如果您正確地輸入公式,儲存格 D1 會顯示為:

4 years, 1 months, 6 days


日、月和年的其他公式分解
此公式也能分解成個別的日、月和年區段,如下所示。

注意 如果您複製並貼上這些公式,請確定沒有分行符號,否則公式將無法運作。
時間區段                     公式
   ------------------------------------------------------------------------
兩個日期之間的          =A2-DATE(YEAR(A2),MONTH(A2)-
剩餘日數,          IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&"days"
忽略年與月
   

兩個日期之間的          =MONTH(A2)- MONTH(A1)+IF(AND(MONTH(A2)
剩餘月數,        <=MONTH(A1), DAY(A2)<DAY(A1)),11,
忽略年與日          IF(AND(MONTH(A2)<MONTH(A1),DAY(A2)>=
DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),
DAY(A2)<DAY(A1)),-1)))&" months"

兩個日期之間的        =YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<
完整年數                MONTH(A1),AND(MONTH(A2)=MONTH(A1),
DAY(A2)<DAY(A1))),1,0)&" years"
注意 先前的公式中,&" days"、&" months" 和 &" years" 是選用項目。如此可讓您將結果區分為日、月和年。

如何尋找兩個日期之間的週間日數

若要尋找兩個日期之間的週間日數 (開始和結束日期分別在 A1 和 A2 儲存格中輸入),請依照下列步驟執行:

1. 建立新的活頁簿。
2. 按一下 [空白活頁簿]
3. 在活頁簿中輸入下列資料:
A1:03/25/94?A2:05/01/98

4. 在儲存格 D1 中輸入下列公式:

=NETWORKDAYS(A1,A2)"1071" 會在儲存格 D1 中顯示結果。

注意: 如果您看到結果顯示為 #NAME,請按一下 Excel 2003 [工具] 功能表中的 [增益集]。按一下以選取 [分析工具箱] 核取方塊,如果出現提示詢問您是否要安裝,請按一下 [是]。在 Excel 2007 和 2010 中,[分析工具箱] 增益集已內建。

如何遞增日期

若要依年數、月數或日數增加日期,請使用下列公式,其中 reference 是日期值或包含日期的儲存格參照,而 value1value2value3 是分別用來增加年、月和日的遞增值。
=DATE(YEAR(reference)+value1,MONTH(reference)+value2,DAY(reference)+value3)


例如,若要將日期增加一個月,公式為:
=DATE(YEAR(DATEVALUE("6/20/96")),MONTH(DATEVALUE("6/20/96"))+1,
DAY(DATEVALUE("6/20/96")))

如何使用時間公式

如何計算經過時間

將一個儲存格的內容減去另一個儲存格的內容以尋找兩者之間的經過時間量時,結果會是一個序列值,用來代表經過的時數、分鐘數和秒數。若想讓這個數字較容易閱讀,請在包含結果的儲存格中使用 h:mm 時間格式。

在下列範例中,如果儲存格 C2 和 D2 包含公式 =B2-A2,而儲存格 C2 的格式為通用格式,則儲存格會顯示為十進位小數 (在此例中為 0.53125,此序列值代表 12 小時又 45 分)。
A1:開始時間   B1:結束時間    C1:差異  D1:差異
(通用)       (h:mm)
A2:6:30 AM      B2:7:15 PM     C2:0.53125     D2: 12:45

如果開始時間與結束時間橫跨午夜,您必須考慮 24 小時制時間差異。若要這麼做,您可以新增數字 1,代表一個 24 小時期間。例如,您可以設定下列表格,允許時間橫跨午夜。
A1:開始時間   B1:結束時間    C1:差異  D1:差異
(通用)       (h:mm)
A2:7:45 PM       B2:10:30 AM    C2:0.614583333  D2: 14:45


若要設定此表格,請在儲存格 C2 與 D2 中輸入下列公式:
=B2-A2+IF(A2>B2,1)

如何累積大於 24 小時的時數和分鐘數

如果您想正確顯示大於 24 小時的時間,可以使用內建的 37:30:55 格式。如果想改用自訂格式,則須以括號括住格式的小時參數,例如:
[h]:mm

如何將日期轉換為十進位小數相等值

若想將序列化日期 (h:mm:ss) 轉換為十進位小數 (0.00),您必須先將序列值轉換為 24 小時制,再轉換為十進位小數。方法是將時間乘以 24,如下所示
=Time*24
其中 Time 是您要從時間格式轉換為十進位小數的數字,此數字可以是儲存格參照或 TIMEVALUE 函數中的數字字串。

例如,如果儲存格 A1 包含 "4:30" 的時間,代表 4 小時又 30 分,公式為:
=A1*24
結果為 4.5。

如果儲存格同時包含日期和時間,請使用下列公式:
=(Time-INT(Time))*24
例如,如果儲存格 A1 顯示為 "6/20/96 4:30 AM",則公式為:
=(A1-INT(A1))*24
結果為 4.5。

如何將十進位小數轉換為日期相等值

若想將十進位小數 (0.00) 轉換為其序列日期相等值 (h:mm:ss),您必須先將序列值轉換為 24 小時制,再轉換為十進位小數。方法是將時間除以 24,如下所示
=Time/24
其中 Time 是您要從十進位小數轉換為日期序列值的數字,可以是儲存格參照或實數。例如,如果儲存格 A1 有一個值 4.5 代表 4 小時又 30 分,公式為:
=A1/24
結果為 4:30。

如何在 Microsoft Excel for Mac 和 Excel for Windows 之間傳輸檔案

依預設,Excel for Mac 使用 1904 日期系統,而 Excel for Windows 使用 1900 日期系統。這表示當您在 Excel for Mac 中輸入序列值 1 並將其格式化為日期,Excel 會將其顯示為 1/2/1904 12:00 a.m。Excel for Windows 則會將序列值 1 顯示為 1/1/1900 12:00 a.m

如果您從 Excel for Mac 傳輸檔案至 Excel for Windows,這項日期系統差異應不致發生問題,因為日期系統儲存在每個檔案中。不過,如果您在源自不同平台之不同日期系統的檔案之間進行複製並貼上,顯示的日期可能會與正確日期有 4 年又 1 天的差距。

若要在 Excel for Windows 中變更為 1904 日期系統,請依照下列步驟執行:
  1. 在 Excel 2003 的 [工具] 功能表上,按一下 [選項]。在 Excel 2007 和更新版本中,請移至 [Excel 選項]
  2. 在 Excel 2003 中,按一下 [計算] 索引標籤,然後按一下以選取 [1904 日期系統] 核取方塊。在 Excel 2007 和更新版本中,按一下 [進階] 選項。向下捲動至 [計算此活頁簿時:] 然後選取 [使用 1904 日期系統] 核取方塊。
若要在 Excel for Mac中變更為 1904 日期系統,請依照下列步驟執行:?
  1. [Excel] 功能表上,按一下 [偏好設定]
  2. [公式和清單] 下方選擇 [計算]
  3. [活頁簿選項] 下方按一下以清除 [1904 日期系統] 核取方塊

?考

如需詳細資訊,請按一下下面的文章編號,檢視「Microsoft 知識庫」中的文章:
214233 Excel 中轉換為未知的數字格式的文字或數字 (機器翻譯)
214386 Microsoft Excel 中的最大時間 (機器翻譯)
241072 在 Excel 中檢視公式時日期和時間顯示為序列值 (機器翻譯)
264372 如何控制及瞭解 Excel 中儲存格格式對話方塊的設定

屬性

文章編號: 214094 - 上次校閱: 2013年12月2日 - 版次: 2.0
這篇文章中的資訊適用於:
  • Microsoft Office Excel 2003
  • Microsoft Office Excel 2007
  • Microsoft Excel 2010
關鍵字:?
kbnomt kbgraphxlink kbscreenshot kbdatetime kbautomation kbprogramming kbfunctions kbhowtomaster kbmigrate KB214094
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應,不負任何擔保責任。Microsoft及(或)其供應商謹此聲明,不負任何對與此資訊有關之擔保責任,包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。

提供意見

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com