問題情境

昨天韓老師講了 ，有位朋友提出問題：

韓老師：公式不對！為什麼8點之前打卡，也是遲到？

韓老師查看他傳過來的數據表，時間的格式是這樣的！如下表：

表面上看起來是僅僅是時間，但真實數字是日期+時間，只不過通過單元格格式設置為只顯示時間。

韓老師原來講過：

日期在EXCEL裡是整數，是距1900年1月1日的天數；

時間是小數，24小時是一天，所以24點是1，其他時間都是小數。

在這個表中打卡時間是日期+時間，就不能直接和1/3（8點）比較，因為日期+時間肯定都大於1/3，直接套用韓老師昨天那篇文章中的公式就會出錯。

公式實現

一、統計是否遲到

在C2單元格輸入公式：

=IF(--TEXT(B2,"hh:mm:ss")>1/3,"遲到","")

並往下填充，即可計算出每位員工是否遲到。

如下圖：

TEXT(B2,"hh:mm:ss")：

將日期+時間的單元格值，通過TEXT函數只保留時間，精確到秒。TEXT是文本函數，得到的時間是文本格式。

--TEXT(B2,"hh:mm:ss")：

通過減負運算，將文本格式的時間轉換為數值格式。

IF(--TEXT(B2,"hh:mm:ss")>1/3,"遲到","")：

將數值格式的時間與1/3（8點）比較，如果大於1/3，即是晚於8點，為遲到，否則返回空值。

二、直接利用打卡時間，統計遲到人數

在B17單元格輸入公式：

=SUMPRODUCT(N(--TEXT(B2:B16,"HH:MM:SS")>1/3))

即可直接依據打卡時間，統計出遲到總人數。

如下圖：

本公式中的TEXT與減負（--）運算參考上一部分是否遲到公式的解釋；SUMPRODUCT的用法參考

閱讀更多 韓老師講office 的文章

關鍵字: 遲到 Excel 打卡