討論怎么用Excel計算出相差的工時數，從而挖掘一些關于時間計算方面的技巧，至少以后遇到類似問題時可以少走彎路。

技巧1：直接相減計算時間差

如果不考慮休息時間的話，要計算工時可以直接用下班時間-上班時間，如果還要在工時后面增加“小時”兩個字，就需要用到TEXT函數。公式為=TEXT(C2-B2,"h小時")，操作過程看動畫演示。

?

在這個公式中，用到了表時間的格式代碼“h”，表示取小時數。在代碼后可以自己添加需要顯示的單位。

技巧2：數字和時間之間的換算

如果要在工時中扣除休息時間，只需要在原工時中減去休息時間即可，但公式結果卻出現了錯誤：

?

這里有一個問題，上班時間和下班時間數字是時間格式，而休息時間數字卻是數字格式。在EXCEL中，一個自然數對應一個日期，而0到1之間的小數則對應一個時間。在本例中，休息時間是自然數，可以直接參與日期計算但不能參與時間計算，，它需要進行轉換，才能與時間格式的數字進行運算，轉換方法是將這個數除以24。所以正確的公式應該是=TEXT(C2-B2-D2/24,"h小時")，結果如圖所示：

?

注意：這里有一個坎，很多對EXCEL時間不了解的人會對C2-B2-D3/24心存疑惑。他們認為D3/24是一個小數，C2-B2=21-9=12,然后12減去一個小數怎么就等于10呢？實際上，EXCEL并非按21-9=12這么來計算時間的。9:00或者21:00只是顯示給我們看的時間格式數字，它們對應的真正的時間值是0.375（9/24）、0.875(21/24)。C2-B2-D3/24=0.875-0.375-0.08333=0.41667。最終時間值0.41667再顯示為時間格式，就成了0.41667*24=10小時。

如果繞不過彎，那你也可以直接把休息時間的“2”改為“2:00”，則可以直接使用=TEXT(C2-B2-D2,"h小時")計算出工時。

技巧3：計算排除周末的工作日天數

計算與工作日有關的問題時經常會用到NETWORKDAYS.INTL函數，如果要排除的休息日是固定的，在不考慮特殊節假日的情況下，只需要使用三個參數，函數格式為：NETWORKDAYS.INTL（開始日期，結束日期，周末）。996模式的工作天數可以用公式“=NETWORKDAYS.INTL("2018-1-1","2018-12-31",11)”計算出來：

?

第三參數代碼11表示僅星期日休息，在填寫第三參數時，其他代碼會有提示：

?

很明顯，955模式只需要將第三參數改為“1”即可，表示星期六和星期日都休息。

這里需要說明的是，在公式中直接輸入日期時，需要將日期放在引號中。

在本例中省略的函數的第四參數是除了周末的特殊節假日，可以單獨輸入，也可以直接引用單元格。

公式=NETWORKDAYS.INTL("2018-1-1","2018-12-31",11,{"2018-1-1","2018-5-1","2018-10-1"})會在去掉周末的基礎上排除元旦，五一和十一這三天：

?

也可以通過單元格區域進行排除：

公式為=NETWORKDAYS.INTL("2018-1-1","2018-12-31",11,I2:I4)

?

技巧4：防止小時發生進位

工作天數有了，每天的工時有了，相乘即可得到全年工作時數，然而公式的結果卻出乎預料：

?

是公式錯了嗎？

非也！

在公式“=TEXT(F2*(C2-B2-D2/24),"h小時")”中，F2是年度工作天數313天，“C2-B2-D2/24”表示每天工時10小時，10小時在EXCEL中對應的數字是0.4166666667，二者的乘積130.4166666667是一個帶小數：

?

時間代碼h只能顯示數字小數部分對應的小時數（即0.4166666667對應的10小時），整數部分（130）對應的是日期就無法顯示。

這種問題在對時間求和的時候經常會遇到，解決方法也很簡單，給時間代碼h加上一對中括號即可，正確的公式為：=TEXT(F2*(C2-B2-D2/24),"[h]小時")

?

技巧5：對含有單位的數據進行計算

平時工作中經常會遇到在數字后面加單位的需求，看起來舒服，但要進行計算時就頭疼了。正確的解決方法是使用自定義格式加單位，操作方法如動態演示：

?

使用自定義格式添加單位，數據格式并沒有發生變化，所以不管求和還是其他計算，都很方便。

但有些時候已經填好了單位，再把單位去掉用自定義格式添加單位其實也挺麻煩的。以下通過計算帶單位的996和955之間全年的時間差，介紹兩個常用的公式。

公式1：通用法則

通用法則需要使用SUMPRODUCT和SUBSTITUTE這兩個函數，原理是利用SUBSTITUTE將數據中的單位替換為空，再使用SUMPRODUCT對數組求和或求差，公式為：=SUMPRODUCT(SUBSTITUTE(G2:G3,"小時","")*{1;-1})&"小時"

?

SUBSTITUTE(G2:G3,"小時","")將單元格G2、G3中的單位“小時”替換為空，結果為{"3130";"2088"}，是兩個文本型的數字：

?

將兩個文本型數字與常量數組{1;-1}相乘后變成一正一負的兩個數{3130;-2088}，同時完成了文本數值轉為數字的過程：

?

最后使用SUMPRODUCT完成求和。

公式2：個性法則

個性法則是根據問題的具體特點設計的公式，在本例中，要計算的數據都是單元格的最左邊4位，可以使用LEFT函數分別提取出來后進行相減，公式為：=(LEFT(G2,4)-LEFT(G3,4))&"小時"

?

這個公式相對很簡單，也好理解，就本例而言是可行的，但不具有通用性。