勤務時間の合計が24時間を超えると狂う日

kamitool 運営者・個人開発の記録

1週間分の勤務時間をExcelで合計したら、24時間を超えたところで表示がおかしくなった。セルの書式をいじって直そうとしていた話と、そこから作ったツールの話を書く。

合計が「1900/1/1 4:30」になった

1週間分の勤務時間、「8:30」「7:45」「9:15」……という記録をSUM関数で合計した。5日分を足すと本来40時間を超えるはずが、セルには「1900/1/1 4:30」のような、日付とも時刻ともつかない値が表示された。

原因を調べると、Excelは時刻を「1日を1」とする小数として扱っていて、合計が24時間（1日）を超えると、時刻ではなく日付に繰り上がって表示される仕組みだと分かった。中身の数値は合っているのに、表示形式が追いついていなかった。

セルの書式を直しても、次の月にはまた忘れる

解決策自体は調べれば出てきた。セルの書式設定を開き、「時刻」の中から[h]:mmという特殊な表示形式を選ぶと、24時間を超えても「40:30」のようにそのまま表示される。

ただ、この設定は月に1回、勤務時間を集計するときにしか使わない。次の月には、書式設定のどこにその選択肢があったかを忘れていた。「時刻」のカテゴリの中に埋もれていて、毎回同じ場所を探し直す羽目になった。

気づいたのは「時間の入れ物を変えればいい」ことだった

ある日、そもそも時刻という入れ物を使い続けているのが問題だと気づいた。時刻はもともと「1日の中の一点」を表すためのもので、合計時間を表すのには向いていない。

合計だけを知りたいなら、時刻としてではなく、最初から「経過時間」として計算すればいい。すべての時間を分に変換して合計し、最後に時:分の形に戻せば、24時間を超えても崩れない。

実際にどう動くか

時間の一覧を貼り付けると、合計を計算して24時間を超えてもそのまま表示する。

合計は「41:00」のような時:分の表示だけでなく、小数の時間（41.0時間）や分（2,460分）にも同時に換算して表示する。給与計算のソフトによって必要な単位が違うので、そのまま貼り戻せる形を選べるようにしている。

書式ではなく計算そのものを変えた理由

Excelの書式設定でも解決できるが、書式は見た目を変えるだけで、値そのものは日付を含んだ小数のままになる。他のシステムに数値として渡すときに、この内部的なズレが原因で計算が狂うことがある。このツールは、最初から時間を分単位の整数として扱うので、渡した先でも安全に計算できる。

作ってみて変わったこと

月末の勤務時間集計で、書式設定のメニューを開かなくなった。貼り付けるだけで、24時間を超えた合計もそのまま出てくる。

この不具合は、気づかずに間違った合計をそのまま提出してしまう危険もある。「1900/1/1」という表示は明らかにおかしいので気づけるが、書式をいったん直した後に別の要因でまた24時間を超えると、今度は静かに間違った値になることがある。今は合計を出す作業そのものを、時刻という入れ物に頼らない方法に切り替えたので、この種の不安がなくなった。

使うときの注意

時間の合計を給与計算に使う場合は、就業規則の丸め方（1分単位・5分単位・15分単位など）を先に確認してから、このツールの結果に反映させている。時間の合計そのものは正確に出せても、丸め方の判断は制度によって異なるため、ツールでは丸めず、実際にかかった時間をそのまま合計するようにしている。

よくある質問

関連する読みもの・ツール