01
日期和時間數(shù)據(jù)的合并
下圖為某單位員工刷卡考勤的部分記錄,需要根據(jù)B列的刷卡日期和C列的刷卡時間,得到日期和時間合并后的數(shù)據(jù)。
在D2單元格中輸入以下公式,并向下復制到D10單元格,即可得到日期和時間合并后的數(shù)據(jù)。
=B2+C2
02
計算故障處理時長
下圖為某運營商寬帶故障報修記錄表的一部分,需要根據(jù)C列的接單時間和E列的處理時間,計算故障處理時長。
在F2單元格中輸入以下公式excel輸入公式后返回為0,并向下復制到F6單元格。
=INT((E2-C2)*1440)
一天有1440分鐘,要計算兩個時間間隔的分鐘數(shù),只要用終止時間減去開始時間,再乘上1440即可。最后用INT函數(shù)舍去計算結果中不足一分鐘的部分,計算出時長的分鐘數(shù)。
如果需要計算兩個時間間隔的秒數(shù),可使用以下公式。
=(E2-C2)*86400
一天有86400秒,所以計算秒數(shù)時使用結束時間減去開始時間,再乘上86400。
除此之外,使用TEXT函數(shù)能夠以文本格式的數(shù)字返回兩個時間的間隔。
以下公式返回取整的間隔小時數(shù)。
=TEXT(E2-C2,"[h]")
以下公式返回取整的間隔分鐘數(shù)。
=TEXT(E2-C2,"[m]")
以下公式返回取整的間隔秒數(shù)。
=TEXT(E2-C2,"[s]")
03
計算員工在崗時長
下圖所示為某企業(yè)員工加班考勤的部分記錄,需要根據(jù)C列的上班打卡時間和D列的下班打卡時間,計算員工的加班工作時長。
如果在E2單元格中使用公式“=D2-C2”計算時間差,由于部分員工的離崗時間為次日凌晨,僅從時間來判斷,離崗時間小于到崗時間,兩者相減得出負數(shù),計算結果會出現(xiàn)錯誤。
通常情況下,員工在崗的時長不會超過24小時。如果下班打卡時間大于上班打卡時間,說明兩個時間是在同一天,否則說明下班時間為次日。
在E2單元格中輸入以下公式,并向下復制到E10單元格。
=IF(D2>C2,D2-C2,D2+1-C2)
IF函數(shù)判斷D2單元格的下班打卡時間是否大于C2單元格的上班打卡時間,如果條件成立,則使用下班時間直接減去上班時間。否則用下班時間加1后得到次日的時間,再減去上班時間。
公式也可以簡化為:
=IF(D2>C2,D2,D2+1)-C2
還可以借助MOD函數(shù)進行求余計算。
=MOD(D2-C2,1)
用D2單元格的下班時間減去C2單元格的上班時間后,再用MOD函數(shù)計算該結果除以1的余數(shù),返回的結果就是忽略天數(shù)的時間差。
04
計算員工技能考核平均用時
下圖所示為某企業(yè)員工技能考核表的部分數(shù)據(jù),B列是以文本形式記錄的員工操作用時,需要計算員工的平均操作時長。
將D2單元格格式設置為“時間”,然后輸入以下數(shù)組公式,按組合鍵,計算結果為“0:01:12”。
=SUM(--TEXT({"0時","0時0分"}&B2:B10,"h:m:s;;;!0"))/9
由于B列的時間記錄是文本內容,因此,Excel無法直接識別和計算。
使用字符串“{"0時","0時0分"}”與B2:B10單元格的內容連接,變成9行兩列的內存數(shù)組“{"0時1分18秒","0時0分1分18秒";"0時59秒","0時0分59秒";…;"0時1分27秒","0時0分1分27秒"}”。
Excel 將“0時0分0秒”樣式的文本字符串識別為時間,將“0時0秒”“0時0分”“0分0秒”等樣式的字符串仍然識別為文本。
TEXT函數(shù)的第二參數(shù)使用“h:m:s;;;!0”,將時間樣式的字符串轉換為“h:m:s”樣式,非時間樣式的文本字符串強制顯示為0。計算結果如下。
{"0:1:18","0";"0","0:0:59";…;"0:1:27","0"}
TEXT函數(shù)計算出的結果仍然為文本,加上兩個負號,即負數(shù)的負數(shù)為正數(shù),通過減負運算將文本結果轉換為時間序列值。
最后將SUM函數(shù)的求和結果除以總人數(shù)9,得到考核平均用時。
05
從混合內容中提取時間和日期數(shù)據(jù)
從考勤機中導出的刷卡記錄往往同時包含日期和時間,如下圖所示,需要在C列和D列分別提取出B列刷卡記錄中的日期和時間。
由于時間和日期數(shù)據(jù)的實質都是序列值,因此,既包含日期又包含時間的數(shù)據(jù)可以看作是帶小數(shù)的數(shù)值。其中,整數(shù)部分為代表日期的序列值,小數(shù)部分為代表時間的序列值。
在C2單元格中使用以下公式提取日期數(shù)據(jù)。
=INT(B2)
=TRUNC(B2)
使用INT函數(shù)或TRUNC函數(shù)提取A列數(shù)值的整數(shù)部分,結果即為代表日期的序列值。
在D2單元格中可使用以下公式提取時間數(shù)據(jù)。
=B2-INT(B2)
=MOD(B2,1)
使用MOD函數(shù)計算A2單元格與1相除的余數(shù),得到A2數(shù)值的小數(shù)部分,結果即為代表時間的序列值。如果結果顯示為小數(shù),可將單元格格式設置為“時間”格式。
除此之外,也可以使用TEXT函數(shù)完成日期時間的提取,以下公式可以提取出B列中的日期。
=--TEXT(B2,"e-m-d")
格式代碼使用“e-m-d”,即“年-月-日”。
以下公式可以提取出B列中的時間。
=--TEXT(B2,"h:m:s")
格式代碼使用“h:m:s”,即“時:分:秒”。
06
將英文月份轉換為月份數(shù)值
如下圖所示,A列為英文的月份名稱,需要在B列轉換為對應的月份數(shù)值。
在B2單元格中輸入以下公式,并向下復制到B10單元格。
=MONTH(A2&1)
使用連接符“&”將A2單元格與數(shù)值“1”連接,得到新字符串“Apr1”,成為系統(tǒng)可識別的文本型日期樣式,再使用MONTH函數(shù)提取出日期字符串中的月份。
YEAR、MONTH和DAY函數(shù)均支持數(shù)組計算,在按時間段的統(tǒng)計匯總中被廣泛應用。
07
匯總指定時間段的銷售額
下圖為某單位2017年銷售記錄表的部分內容,A列是業(yè)務發(fā)生日期excel輸入公式后返回為0,D列是業(yè)務金額,需要計算上半年的業(yè)務總額。
可以使用以下公式完成匯總。
=((MONTH(A2:A13)
MONTH函數(shù)返回A2:A13單元格中日期數(shù)據(jù)的月份值,結果為:
{1;3;6;2;6;3;4;6;7;8;6;11}
因為要計算1~6月份的業(yè)務總額,所以要判斷月份值是否小于7。
用“MONTH(A2:A13)
今天給大家介紹了7個平常會用到卻不怎么好找的公式,希望對大家有所幫助!系列的下篇教程將在周一為大家推送,讓我們一起努力吧!
:每日干貨,全年不休息(公眾號ID:)
掃一掃添加老師微信
在線咨詢Excel課程