シフト表に出勤・退勤の時刻を入力したのに、勤務時間の合計がうまく計算できない。あるいは、手で足し算したら数字が合わない。そういった場面で検索した方に向けて、この記事では「時刻データから勤務時間を出す計算式」と「月間・週間の合計を出す手順」を具体的に説明します。
最初の答えを先に書いておきます。スプレッドシートで時刻を扱うとき、「9:00」「17:30」のような表示の裏では小数の数値が動いています。退勤時刻から出勤時刻を引けば差が出ますが、合計が「24時間を超える」ときや「休憩を引く」ときに表示がくずれる落とし穴があります。この記事ではその原因と回避策を順に示します。
スプレッドシートが時刻をどう持っているか
ExcelもGoogleスプレッドシートも、時刻を「1日=1」とする小数で管理しています。たとえば「12:00」は0.5、「6:00」は0.25として内部では保存されています。
この仕組みを知っておくと、計算がなぜ合わないかが理解しやすくなります。
- 「17:30 − 9:00」を計算すると結果は「0.354166…」という小数になります。
- これをセルの書式で「時刻(h:mm)」と指定すれば「8:30」と表示されます。
- しかし合計が24時間を超えると、書式を「h:mm」のままにしていると「0:00」や「1:15」のような見た目になってしまいます。これは24時間で繰り上がるためです。
合計セルだけ書式を「[h]:mm」(角括弧付き)に変えると、24時間を超えても正しく「32:15」のように表示されます。これがよく起きる失敗の原因と対処です。
基本の計算式:1人・1日の勤務時間
架空の例で説明します(以下はすべて架空のデータです)。
表は横にスクロールできます
| セル | 内容 |
|---|---|
| B2 | 出勤時刻(例:9:00) |
| C2 | 退勤時刻(例:18:00) |
| D2 | 休憩時間(例:1:00) |
| E2 | 実働時間 |
E2に入力する式は次のとおりです。
=(C2-B2)-D2
休憩時間が「分数」で記録されているチームもあります。たとえば「60」(分)と数値で入れている場合は、時刻に換算してから引く必要があります。
=(C2-B2)-D2/1440
1440は1日の分数(60分×24時間)です。D2が「分」の数値なら、1440で割って「日」の単位に変換してから引きます。
画面の表示は版や設定により異なりますので、書式設定の場所は公式のヘルプで確認してください。
日付をまたぐ夜勤の場合
退勤時刻が翌日になる場合(例:出勤22:00、退勤06:00)は、単純に引き算をするとマイナスになります。このときはIF式で補正します。
=IF(C2<B2, C2-B2+1, C2-B2)-D2
C2<B2(退勤が出勤より小さい、つまり日付をまたいでいる)なら、「1」(=1日分)を足して正の値にします。ただし、出勤・退勤を別々のセルに時刻だけで入れている場合の補正です。日付と時刻を一つのセルに入れている(例:「2026/10/21 22:00」)なら補正は不要で、単純な引き算で正しく計算されます。
週間・月間の合計を出す手順
1人分の実働時間(E列)が並んだら、週や月の合計はSUM関数で出せます。
=SUM(E2:E8)
このとき、合計セルの書式が「h:mm」のままだと24時間超えで表示がくずれます。合計セルを右クリック→セルの書式設定→「ユーザー定義(カスタム書式)」で [h]:mm と入力してください。
「時間」の数値として合計したい場合(給与計算ソフトに「8.5時間」のように入力するなど)は、SUM結果を24倍します。
=SUM(E2:E8)*24
この結果は「8.5」のような小数になります。セルの書式は「数値」か「標準」にしておかないと、時刻の表示に戻ってしまいます。
複数人のシフトを一覧で集計する構成例
小規模なチームで5〜10人分のシフトを1枚のシートにまとめる場合、次のような構成が扱いやすいです(架空の例です)。
表は横にスクロールできます
| 氏名 | 日付 | 出勤 | 退勤 | 休憩(分) | 実働(h:mm) |
|---|---|---|---|---|---|
| 山田A | 10/1 | 9:00 | 17:30 | 60 | 7:30 |
| 鈴木B | 10/1 | 13:00 | 20:00 | 45 | 6:15 |
| 山田A | 10/2 | 9:00 | 18:00 | 60 | 8:00 |
この形(1行1シフト)にしておくと、あとでSUMIF関数を使って「氏名ごとの月間合計」を出せます。
=SUMIF(A:A,"山田A",F:F)
A列が氏名、F列が実働時間(時刻の数値)の場合の例です。結果の書式も [h]:mm にしてください。
「氏名ごとに横に展開した表」は見やすい反面、関数で集計しにくくなります。集計を後でも使いたいなら、縦に積む形(1行1シフト)を最初から選んでおくほうが後の手間が少なくなります。
よくある失敗と確認ポイント
合計がゼロまたは変な数字になる
→ 時刻として入力されているか確認します。「9:00」と打ってもテキストとして入っている場合があります。セルを選んで書式を確認するか、=ISNUMBER(B2) でTRUEが返るか見てください。
休憩を引いたら負の値が出た → 休憩時間が実働より長くなっていないか確認します。入力ミスの可能性が高いです。
コピーして貼り付けたら式がずれた
→ 絶対参照($B$2 のように$を付ける)と相対参照の使い分けを確認してください。行方向にコピーするなら列は固定しなくてよいです。
Excelで作ったファイルをGoogleスプレッドシートで開いたら表示がくずれた
→ [h]:mm のユーザー定義書式は両者でほぼ共通ですが、一部のカスタム書式は変換されないことがあります。集計列の書式を開き直したあとに再設定してください。
作業の前に元のファイルをコピーしてバックアップを残しておくと、式を変えて表示がおかしくなっても元に戻せます。
次の行動
まず1行だけ架空のデータを入力し、=(C2-B2)-D2/1440 が正しく動くか確認してください。合計セルに [h]:mm の書式を当てて24時間超えの表示も確かめると、月間集計に進む前に問題を早く見つけられます。複数人分に広げるときは、1行1シフトの縦並び構成にしておくとSUMIFでの集計が後から楽になります。
この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。
