勤務時間の合計が24時間を超えると0に戻る?「[h]:mm」で40時間を正しく表示する方法【LibreOfficeで計算して確認】

日ごとの勤務時間を合計したら、40時間のはずが「16:00」と表示された。勤務表を作るとき、よく起きることです。この記事では、24時間を超えても正しく表示する書き方と、時間・分・小数の時間に分ける式を、実際に計算して確かめた結果だけで紹介します。

出典: 筆者がLibreOfficeで計算して確認(検証スクリプト: site/verify/hours_over24.py)。外部サイトの説明は参照していません。Excelでは未確認です。使う関数はExcelにもありますが、本番の表に使う前に数行で確かめてください。

先に結論

合計の数字は合っていて、表示の書式が「24時間で0に戻る」形になっていることが原因です。
書式を [h]:mm(時間を角かっこで囲む)にすると、24時間を超えても「40:00」「167:30」のように表示されます。
給与などの計算に使うなら、表示ではなく 時間を小数にする式(合計×24) や 分の整数 にしておくと扱いやすくなります。

何が起きているのか

Excelなどの表計算では、時刻は「1日 = 1」の小数として持ちます。8:00は 1/3(約0.3333)です。8時間の日を5日合計すると、中身は 5/3(約1.6667)で、合計の値としては正しい40時間です。

ただし h:mm という書式は「1日の中の時刻」を表すものなので、1日(24時間)を超えた分を捨てて表示します。結果、40時間が16時間に見えてしまいます。

手順1: 表示を `[h]:mm` にする

合計のセルに =SUM(A2:A6) を入れ、書式として [h]:mm を指定します。この記事では、同じ書式を TEXT 関数で指定して確かめました。

=TEXT(SUM(A2:A6),"[h]:mm")   → 24時間を超えても表示される
=TEXT(SUM(A2:A6),"h:mm")     → 24時間で0に戻る

8:00の日が5日の合計を計算した結果です。

合計の内容	`[h]:mm`	`h:mm`
8:00 × 3日	24:00	0:00
8:00 × 5日	40:00	16:00
12:00 + 12:01	24:01	0:01
23:59 × 2日	47:58	23:58
8:00 × 20日 + 7:30	167:30	23:30
1:00 × 25日	25:00	1:00
8:00(1日だけ)	8:00	8:00

見てのとおり、合計が24時間未満なら2つの書式は同じで、気づかないまま作りがちです。月末に合計が24時間を超えたときに初めて「0に戻る」ことに気づきます。

手順2: 時間・分・小数の時間に分ける

合計のセルを A8 とします。

合計の分(整数): =ROUND(A8*1440,0)
時間の部分     : =INT(ROUND(A8*1440,0)/60)
分の部分       : =MOD(ROUND(A8*1440,0),60)
小数の時間     : =ROUND(A8*24,2)

1日は1440分なので、合計×1440で分になります。ROUNDで整数にしてから割ると、小数の誤差による「59分になってしまう」ようなずれを避けられます。

例として、12:00 + 12:01 の合計(24時間1分)は次のとおりです。

項目	結果
合計の分	1441
時間の部分	24
分の部分	1
小数の時間(小数第2位まで)	24.02

49通りの合計すべてで、この6項目が期待値と一致しました(乱数で作った40通りを含みます)。時給を掛けるなど金額の計算に使うときは、A8*24 の小数の時間か、分の整数を使う方が、表示の書式に左右されません。端数をどこで丸めるかは、時給の規則に合わせて自分で決めてください(この記事では扱いません)。

落とし穴: 文字列の「8:00」は合計に入らない

見た目は時刻でも、文字列として入っているデータは SUM の合計に入りません。実際に、文字列の「8:00」と、時刻の値の8:00を並べて SUM した結果は次のとおりでした。

式	結果
`=SUM(A1:A2)`	8:00の1つ分(0.3333…)だけ。文字列は無視される
`=ISTEXT(A1)`	TRUE(文字列だと分かる)
`=TIMEVALUE(A1)*24`	8
`=SUM(TIMEVALUE(A1),A2)*24`	16(文字列を時刻に変えれば合計できる)

エラーにならず、黙って少ない合計が出るので注意が必要です。左寄せで表示される、ISTEXTがTRUEになる、といった点で見分けられます。文字列の時刻は TIMEVALUE で時刻の値にできます。なお、他の形式の文字列(「8時00分」など)は検証していません。

注意点: この記事の式ができないこと

日をまたぐ勤務(22:00〜6:00)の時間の出し方、休憩の引き方は、この記事では扱っていません(開始・終了の時刻から勤務時間を出す部分は別の工夫が必要です)。
割増賃金・残業の判定・給与の計算は扱いません。就業規則や法令に基づく判断は、この記事の範囲外です。
Excelでの画面表示は未確認です。

シフトの表から作りたいなら

勤務時間の合計の式は、自分で作った表にそのまま使えます。シフトを1か月分まとめるひな形が欲しいなら、運営者が作った月間シフト表(Excelテンプレート・500円)があります。A4横1枚で印刷でき、15人まで入り、日付と曜日が自動で入ります。勤務時間の合計や給与の計算は入っていません(この記事の式を自分で足す形になります)。Excelの確認はWindowsデスクトップ版16のみです。この記事の方法で足りる場合は、買う必要はありません。

ブラウザだけで試したいときは、無料の時給×勤務時間の簡易計算ツールも使えます(入力は外部に送信されません)。

まとめ

40時間が16時間に見える原因は、合計の値ではなく h:mm という書式
書式は [h]:mm。合計が24時間未満では違いが出ないため、月末まで気づきにくい
計算に使うなら、ROUND(合計*1440,0) の分、または ROUND(合計*24,2) の小数の時間
文字列の「8:00」は SUM に黙って入らない。ISTEXT で調べ、TIMEVALUE で直す
式はいずれもLibreOfficeで計算した結果。Excelでは未確認のため、本番の表に使う前に数行で確かめる

確認方法: この記事の式は、LibreOffice Calcで実際に再計算し、日ごとの勤務時間(h:mm)の合計49通り(8時間×3日・5日、25時間ちょうど、23:59×2、乱数で作った40通りなど)について、TEXT関数の「[h]:mm」と「h:mm」の表示、小数の時間、時間の部分、分の部分、合計の分の計6項目が期待値と一致すること、および文字列の「8:00」が合計に入らないことを検証スクリプト(site/verify/hours_over24.py)で確認しました。TEXT・SUM・ROUND・INT・MOD・TIME・TIMEVALUEはExcelにもある関数ですが、Excelでは未確認です。セルの表示形式として設定した場合の見え方は、TEXT関数で同じ書式を指定して確認したもので、画面の表示そのものは未確認です。外部サイトの記述は参照していません。

参考にした情報

出典: 筆者がLibreOfficeで計算して確認(検証スクリプト: site/verify/hours_over24.py)