外部システムが集計した労働時間と、Excelで再計算した労働時間を突き合わせたところ、15分だけ合わない行が出てきました。丸めのルールも数式も合っているはずなのに、です。
原因は、丸める順序でした。この記事では、時間の丸めで結果が変わる仕組みと、どちらを選ぶべきかの判断基準を整理します。
想定読者は、Excelで出退勤時刻を15分単位などに丸めて、労働時間を計算している方です。
同じデータで15分ずれる例
次のルールで、1日分の実労働時間（休憩なし）を計算します。
- 出勤時刻は15分単位で切り上げる
- 退勤時刻は15分単位で切り下げる
出勤8:48、退勤13:34の場合、順序によって結果が変わります。
| 先に丸める | 差分を丸める | |
|---|---|---|
| 出勤 8:48 | 9:00に切り上げ | そのまま |
| 退勤 13:34 | 13:30に切り下げ | そのまま |
| 計算 | 13:30 − 9:00 = 4:30 | 13:34 − 8:48 = 4:46 → 切り下げて 4:45 |
同じ打刻から、計算の順序が違うだけで4:30と4:45という結果が出ます。
Excelでの書き方
A2に出勤、B2に退勤、C2に休憩時間が入っているとします。
先に丸める方法です。
=FLOOR(B2,TIME(0,15,0))-CEILING(A2,TIME(0,15,0))-C2差分を丸める方法です。
=FLOOR(B2-A2-C2,TIME(0,15,0))休憩1時間を引くと、先に丸める方法は3:30、差分を丸める方法は3:45になり、やはり15分ずれます。
時刻がシリアル値の小数で保存されている仕組みは、日付が数字になる仕組みとVBA・PQでの扱いで整理しています。
なぜ結果が変わるのか
出勤の切り上げは、実際より遅い時刻にそろえるので、労働時間を短くします。退勤の切り下げも、労働時間を短くします。この例では、出勤側で12分、退勤側で4分、合計16分が削られています（4:46 → 4:30）。
一方、差分を丸める方法では、4:46を切り下げるだけなので、削られるのは1分です（4:46 → 4:45）。
丸め処理は、引き算をまたいで分配できません。先に丸めるか後に丸めるかで、削られる量が変わります。
差が出る条件
出勤を切り上げ、退勤を切り下げる場合、次のように整理できます。
- 先に丸めた結果は、差分を丸めた結果より大きくなりません
- 両者の差は、0分か15分のどちらかです
- 差が15分になるのは、出勤側と退勤側で削られた分の合計が、15分以上のときです
先ほどの例は12分＋4分＝16分なので、15分ずれました。出勤8:55、退勤17:05なら、5分＋5分＝10分なので、ずれません。
出勤を8:00〜9:00、退勤を16:00〜17:00に1分刻みで振った全パターン（3,721通り）で確認したところ、差は0分が2,041通り、15分が1,680通りで、それ以外はありませんでした。
よくある失敗
1. どちらの順序で丸めているかを確認しない
外部システムの集計値と突き合わせるとき、数式が正しくても数値が合わないことがあります。原因を探し回る前に、相手が先に丸めているのか、差分を丸めているのかを確認します。
2. 「ときどき15分ずれる」で片づける
全行がずれるわけではないので、入力ミスやバグと勘違いしがちです。ずれるかどうかは、削られた分の合計が15分以上かで決まります。ずれた行だけを見ても原因はつかめないので、ずれなかった行と並べて比べます。
3. 浮動小数点の誤差を先に疑う
Excelの時刻はシリアル値の小数で持たれているため、丸め関数が想定と違う側に転ぶのではと疑いたくなります。私の環境では、0:00〜23:45の15分刻み96通りで、CEILINGとFLOORが同じ時刻を返しました。15分刻み同士の差分（117通り）をFLOORしても、1段下がることはありませんでした。まずは丸める順序を確認するのが先です。
対処：先に決めるのは「どちらが正か」
- ルールが「打刻時刻そのものを丸める」なら、先に丸める
- ルールが「労働時間を丸める」なら、差分を丸める
- 外部システムの値と照合するなら、そのシステムの仕様に合わせる
どちらが正しいかは、就業規則などの運用ルールで決まります。数式の工夫より、この判断を先にするのが重要です。
応用例：分単位の整数にしてから丸める
時刻のシリアル値のまま丸めることに不安があるときは、分単位の整数に直してから丸める書き方もあります。
=(FLOOR(ROUND(B2*1440,0),15)-CEILING(ROUND(A2*1440,0),15))/1440-C28:48と13:34の例では、先に丸める方法と同じ4:30になります。15分刻みの時刻117通りでは、丸めで値が変わらないことも確認しました。表示はh:mmにします。
月間の合計はSUMで足せます。24時間を超える累計の扱いは、【Excel関数】TIMEVALUEが24時間以上で誤る原因と対処にまとめています。
丸め関数の仕様は、Microsoft SupportのFLOOR関数のページで確認できます。
丸めた値を使って全体を計算すると、同じようにずれることがあります。率の平均でも起きる例は、率の平均はAVERAGEでなくSUM/SUMで解説しています。
丸めた打刻から再計算した時間を、システムの集計値と突き合わせるときの閾値の決め方は、【Excel】打刻と集計値の不整合を検知するフラグ列の設計で実機検証しています。
金額の端数を五捨六入にする式は、【Excel関数】五捨六入の式｜0円ガードの落とし穴で扱っています。ROUNDUPで0.5を引く書き方と、0円まわりの落とし穴を実測しました。
まとめ
- 時間の丸めは、先に丸める場合と差分を丸める場合で結果が変わる
- 出勤切り上げ・退勤切り下げでは、差は0分か15分。削られた分の合計が15分以上のときに差が出る
- 数式より前に、どちらの順序が正しいルールかを確認する
コメント