業務の整え方約5分で読めます

シフトの集計|勤務時間の合計

シフトの集計を表したイメージ
記事の内容を表したイメージ(AI生成)

シフト表に出勤・退勤の時刻を入力したのに、勤務時間の合計がうまく計算できない。あるいは、手で足し算したら数字が合わない。そういった場面で検索した方に向けて、この記事では「時刻データから勤務時間を出す計算式」と「月間・週間の合計を出す手順」を具体的に説明します。

最初の答えを先に書いておきます。スプレッドシートで時刻を扱うとき、「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)
山田A10/19:0017:30607:30
鈴木B10/113:0020:00456:15
山田A10/29:0018:00608: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)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ