Excel怎么算加班時長? 根據(jù)考勤打卡數(shù)據(jù)計算加班次數(shù)加班時長技巧

有這樣一份考勤表,現(xiàn)在需要對每位員工考勤做一次加班的統(tǒng)計,最后一次打卡時間大于等于20:00就計算為加班。20:00之后有多次打卡的,以最后一次打卡時間為準。
首先我們來計算加班次數(shù)
=COUNT(0/(--TEXTAFTER(B2:H2,CHAR(10),-1)>=5/6))
公式淺析:
①--TEXTAFTER(B2:H2,CHAR(10),-1)
先使用TEXTAFTER函數(shù)在B2到H2單元格區(qū)域的每個單元格的文本內(nèi)容里,以換行符(通過CHAR(10)來表示換行符這個字符)作為查找參照,提取出每個單元格文本中最后一次出現(xiàn)換行符之后的那部分文本內(nèi)容,通過雙負號轉(zhuǎn)換為數(shù)值(時間)。
②--TEXTAFTER(B2:H2,CHAR(10),-1)>=5/6
將提取的內(nèi)容做邏輯對比,大于等于5/6的,就是加班的日期,5/6=20/24,即20:00時。
③0/(--TEXTAFTER(B2:H2,CHAR(10),-1)>5/6)
0/TRUE=0,0/FALSE=#DIV/0!。由于有些單元格中沒有打卡時間,計算出現(xiàn)N/A錯誤,不影響后面用COUNT函數(shù)計數(shù)。
④=COUNT(0/(--TEXTAFTER(B2:H2,CHAR(10),-1)>=5/6))
最后用COUNT計數(shù),計算出加班次數(shù)。
再來計算加班時長
=SUM(IFERROR(--TEXT(TEXTAFTER(B2:H2,CHAR(10),-1)-5/6,"0.000000;"),))
公式淺析:
①TEXTAFTER(B2:H2,CHAR(10),-1)-5/6
先使用TEXTAFTER函數(shù)提取最后一次打卡時間減去20:00的時間即得到加班的時長,如果最后一次打卡時間小于20:00會出現(xiàn)負數(shù)。
②IFERROR(--TEXT(TEXTAFTER(B2:H2,CHAR(10),-1)-5/6,"0.000000;"),)
使用TEXT函數(shù)將正數(shù)顯示成0.000000格式,負數(shù)不顯示,在TEXT函數(shù)前面加一對雙符號,將文本數(shù)字轉(zhuǎn)換成數(shù)值。
再使用IFERROR函數(shù)屏蔽負數(shù)錯誤值和未打卡單元格的錯誤值。
③=SUM(IFERROR(--TEXT(TEXTAFTER(B2:H2,CHAR(10),-1)-5/6,"0.000000;"),))
最后使用SUM函數(shù)匯總加班時長。
相關文章
excel文本數(shù)據(jù)怎么計算? excel表格計算帶有文本的數(shù)值的技巧
excel中的數(shù)據(jù)計算的時候,發(fā)現(xiàn)是文本數(shù)據(jù),該怎么計算呢?下面我們就來看看excel帶文本計算的技巧2025-04-25這個函數(shù)公式簡直太強了! excel按權重求和計算的技巧
Excel中有一個功能非常強大的函數(shù),它就是SUMPRODUCT函數(shù),既能求和、計數(shù)、權重計算,還能排名等,今天我們就來看看Excel根據(jù)權重求和的技巧2025-04-10Excel工齡計算不再愁! Excel隱藏函數(shù)DATEDIF輕松計算精確到天級的工齡
在日常工作中,我們經(jīng)常需要計算員工的工齡,以便進行人事管理、薪資調(diào)整等工作,本文將詳細介紹如何在Excel表格中計算工齡,并提供多種實用方法供您選擇2025-03-13excel計算中位數(shù)的公式是什么? Excel用公式計算中位數(shù)的技巧
在Excel中,求中位數(shù)可以使用MEDIAN函數(shù),該函數(shù)接受一組數(shù)據(jù)作為輸入,并返回其中位數(shù),MEDIAN函數(shù)使用非常簡單,下面詳細介紹2025-01-29Excel篩選后怎么僅統(tǒng)計可見行? Excel表格數(shù)據(jù)計算只統(tǒng)計顯示數(shù)據(jù)的技巧
Excel表格中的編號、總數(shù)、總價數(shù)據(jù)通過篩選后顯示的數(shù)據(jù)需要計算,怎么只計算顯示數(shù)據(jù),忽略隱藏數(shù)據(jù)呢?詳細請看下文介紹2024-12-24輕松實現(xiàn)行級數(shù)據(jù)計算! Excel2024新函數(shù)BYROW詳解
2024年,Excel引入了令人興奮的新函數(shù)BYROW,這一功能將為用戶在數(shù)據(jù)分析時提供更強大的靈活性和簡便性,下面我們就來看看使用方法2024-12-10快來看看你到底幾歲退休! Excel公式計算延遲退休年齡的技巧
你啥時候可以退休?相比以前多上幾年?今天我們來看看EXCEL通過出生日期計算退休日期的公式,以便批量計算退休日期2024-11-25試試選擇性粘貼+合并計算? 格式不同多excel表對賬別用SUMPRODUCT了
excel多個表格想要核對每個表格的數(shù)據(jù)差異,該怎么核對呢?平時我們都是使用SUMPRODUCT,這次我們換一個技巧,使用試試選擇性粘貼+合并計算試試2024-10-28Excel如何計算相隔天數(shù) Excel計算兩個日期之間相隔天數(shù)的方法
Excel如何計算相隔天數(shù)?其實我們只需要點擊打開公式工具,然后在其子工具欄中點擊打開“日期和時間”工具,接著在下拉列表中找到并打開“DAYS360”公式,最后在彈框中輸入2024-06-13excel怎么計算不同顏色字體的個數(shù)? excel表格按顏色統(tǒng)計字體個數(shù)的技巧
今天老板又扔給小編一份文件,要求統(tǒng)計各字體顏色的單元格個數(shù),字體顏色很相似,下面我們就來看看excel求單元格內(nèi)不同顏色字體個數(shù)的方法2024-06-11