編按：

最近不少互聯(lián)網(wǎng)大佬對(duì)996發(fā)表言論，一時(shí)間996的工作制被推上風(fēng)口浪尖。那996和955之間究竟相差了多少工時(shí)呢？一起來(lái)看看下面這篇文章，你就知道了。

996最近火了！

什么是996呢？是指早上9點(diǎn)上班，晚上9點(diǎn)下班，中午和晚上各有1小時(shí)吃飯時(shí)間，每周工作6天的工作模式。

而955，也就是朝九晚五，中午就餐在工作時(shí)間內(nèi)，但不能休息，共計(jì)8小時(shí)工作時(shí)間，每周五天的工作模式。

那么問(wèn)題來(lái)了，996一年比955多工作了多少小時(shí)?

不算不知道，一算嚇一跳，竟然多了1042小時(shí)，這可是半年的工作量呢?。?！

咱們今天不討論996是否有問(wèn)題,而是討論怎么用Excel計(jì)算出相差的工時(shí)數(shù)，從而挖掘一些關(guān)于時(shí)間計(jì)算方面的技巧，至少以后遇到類似問(wèn)題時(shí)可以少走彎路。

技巧1：直接相減計(jì)算時(shí)間差

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

動(dòng)畫1

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

技巧2：數(shù)字和時(shí)間之間的換算

如果要在工時(shí)中扣除休息時(shí)間，只需要在原工時(shí)中減去休息時(shí)間即可，但公式結(jié)果卻出現(xiàn)了錯(cuò)誤：

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

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

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

技巧3：計(jì)算排除周末的工作日天數(shù)

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

第三參數(shù)代碼11表示僅星期日休息，在填寫第三參數(shù)時(shí)，其他代碼會(huì)有提示：

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

這里需要說(shuō)明的是，在公式中直接輸入日期時(shí)，需要將日期放在引號(hào)中。

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

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

也可以通過(guò)單元格區(qū)域進(jìn)行排除：

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

技巧4：防止小時(shí)發(fā)生進(jìn)位

工作天數(shù)有了，每天的工時(shí)有了，相乘即可得到全年工作時(shí)數(shù)，然而公式的結(jié)果卻出乎預(yù)料：

是公式錯(cuò)了嗎？

非也！

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

時(shí)間代碼h只能顯示數(shù)字小數(shù)部分對(duì)應(yīng)的小時(shí)數(shù)（即0.4166666667對(duì)應(yīng)的10小時(shí)），整數(shù)部分（130）對(duì)應(yīng)的是日期就無(wú)法顯示。

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

技巧5：對(duì)含有單位的數(shù)據(jù)進(jìn)行計(jì)算

平時(shí)工作中經(jīng)常會(huì)遇到在數(shù)字后面加單位的需求，看起來(lái)舒服，但要進(jìn)行計(jì)算時(shí)就頭疼了。正確的解決方法是使用自定義格式加單位，操作方法如動(dòng)態(tài)演示：

動(dòng)畫2

使用自定義格式添加單位，數(shù)據(jù)格式并沒(méi)有發(fā)生變化，所以不管求和還是其他計(jì)算，都很方便。

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

公式1：通用法則

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

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

將兩個(gè)文本型數(shù)字與常量數(shù)組{1;-1}相乘后變成一正一負(fù)的兩個(gè)數(shù){3130;-2088}，同時(shí)完成了文本數(shù)值轉(zhuǎn)為數(shù)字的過(guò)程：

最后使用SUMPRODUCT完成求和。

公式2：個(gè)性法則

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

這個(gè)公式相對(duì)很簡(jiǎn)單，也好理解，就本例而言是可行的，但不具有通用性。

今天分享了五個(gè)技巧，但其中涉及的細(xì)節(jié)知識(shí)點(diǎn)非常多，沒(méi)有進(jìn)行深入討論，有不明白的點(diǎn)可以留言提問(wèn)，共性問(wèn)題會(huì)單獨(dú)發(fā)表教程。

你是996還是955呢？

本文配套的練習(xí)課件請(qǐng)加入QQ群：264539405下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

計(jì)算加班時(shí)間《這樣算你的加班時(shí)間才絕對(duì)不會(huì)少算！》

計(jì)算加班費(fèi)《原來(lái)，用excel計(jì)算加班費(fèi)如此簡(jiǎn)單！》

SUMPRODUCT函數(shù)《Excel必會(huì)函數(shù)之SUMPRODUCT函數(shù)》