シフト表をエクセルで自動計算する方法｜勤務時間・休憩・夜勤・月間合計の求め方

シフト表をエクセルで自動計算する基本は、**「終了日時－開始日時－休憩時間」**です。開始をB列、終了をC列、休憩の分数をD列に入れるなら、計算式の中心は =C7-B7-D7/1440。開始・終了の両方に日付を含めれば、日中の勤務と日付をまたぐ夜勤を同じ方法で扱えます。

ただし、勤務時間を正しく集計するには、引き算だけでは不十分です。未入力を0分と取り違えないこと、異常な行を合計に混ぜないこと、24時間を超える合計を正しく表示することまで整えましょう。以下では、1人分の配布Excelの使い方と、同じ構成を自作するための数式を説明します。

勤務時間は「終了日時－開始日時－休憩時間」で求める

まず決めるのは、計算に使う項目と単位です。本稿では、開始・終了は「年月日と時刻」、休憩時間は「分数」に統一します。

たとえば、9時から18時までの拘束時間は9時間。そこから60分を差し引くと、勤務時間は8時間になります。毎日の結果を数値として保持し、最後に合計する構成です。

シフトの予定と勤務実績を分けて扱う

同じ引き算でも、何を入力したかで結果の意味は変わります。シフト表の予定を使えば予定の勤務時間、実際の始業・終業と休憩時間を使えば、その内容に基づく実績の集計です。

シフト管理と勤怠管理を同じファイルで行う場合も、「予定用」と「実績用」は分けてください。たとえば、予定では18時終了でも、実際には18時20分まで働いたなら、予定の値だけでは実績を表せません。

ここで紹介する表には、打刻を取り込んで予定と照合する仕組みはありません。勤怠管理表として使うときは、確認済みの実績を転記し、予定をそのまま実績として扱わない運用が必要です。

また、実績への修正で予定の記録を上書きすると、後から差分を確かめられなくなります。月別・従業員別に保存する際は、ファイル名や表の上部にも「予定」「実績」の区別を記載しておきましょう。

日時・分数・小数時間の単位をそろえる

Excelの時刻は、1日を基準とする小数で表されます。たとえば、正午は半日なので0.5です。(Microsoft サポート) この扱いから、24時間は1、60分は 1/24、1分は 1/1440 として換算できます。

本稿で使う値の関係を整理すると、次のようになります。

間違えやすいのは「6時間45分」です。小数時間では 6.75 であり、6.45 ではありません。45分は1時間の4分の3だからです。

人が読む欄と、別の計算へ渡す欄を分けると、この取り違えを防げます。本稿の表ではE列を時間と分、F列を小数時間として使います。

配布Excelの列と入力範囲を確認する

配布Excelの「勤務時間」シートは、1人分の明細を扱う構成です。ファイルは次のリンクから開いて保存してください。(シフトラ)

対象範囲は7〜37行目の31行。複数の従業員を同じ明細に混在させず、まず1人分で使い方を確かめましょう。本文の数式をコピーし、手元のエクセルで同じ構成を自作することもできます。

1人分のB〜D列を入力欄として使う

配布ファイルで日々記入するのは、黄色のB〜D列です。それぞれの役割は次のとおりです。

開始・終了は、たとえば 2026/9/1 9:00 と 2026/9/1 18:00 のように記入します。A列に日付が見えていても、B・C列を時刻だけに省略しないでください。

最初はB・C列の年月日がすべて読める列幅にしておくと、翌日の指定や年の取り違えを発見できます。見栄えを整えるのは、計算結果を確認した後で十分です。

休憩0分と未入力を区別する

D列の空欄は「休憩時間がまだ分からない」、0は「差し引く休憩を0分として記入した」という違いがあります。空欄を自動で0分にすると、記入漏れのまま拘束時間全体が合計されてしまいます。

休憩時間が分からない場合は、警告を消すために0を入れるのではなく、予定や実績を確認してください。複数回に分けて取った場合は、その行で差し引く分数の合計をD列に記入します。

勤務がない行はB〜D列をすべて空欄にします。「休」「公休」などを日時欄へ入れる運用とは分けましょう。休日や休暇の区分も残したい場合は、計算対象とは別の欄で管理します。

なお、数値として0を受け付けることと、その勤務に法定休憩が不要であることは別です。後述する架空例にも0分の行がありますが、これは計算の確認用です。

4行の架空例で31時間45分を確かめる

次の表は、配布ファイルの構成に合わせた架空の計算例です。7〜10行目に順番に記入し、E列とF列の結果を確かめてください。

ほかの明細行が空欄なら、E4の合計は 31:45 です。小数時間に換算すると31.75になります。

9月5日の行は、0分を記入したときに9時間が返ることを確かめるためのものです。法令に合う勤務例として扱ったり、そのまま実際のシフトへ転用したりしないでください。

まずこの4行で、日勤・夜勤・45分の控除・0分の扱いを一通り確認します。その後、試験用データを消して実際の対象者の内容に差し替える順序なら、数式の不備と転記ミスを切り分けられます。

図を拡大して見る

自作する場合は入力確認から関数を設定する

自作する場合は、6行目に見出しを置き、7行目を最初の明細にします。配布ファイルをそのまま使う方は、以下を数式の仕組みを確認するために読んでください。

作業順は、G列の入力確認、E列の引き算、F列とA列の補助計算、下の行へのコピーです。先に異常を見つける仕組みを用意すると、誤った数値を結果として見せずに済みます。

手順1：G列で未入力と前後関係を調べる

G7には、次のような式を設定できます。これは配布ファイルの警告条件に合わせた自作例で、警告文は内容が分かる表現にしています。

=IF(COUNTA(B7:D7)=0,"", IF(COUNT(B7:D7)<>3,"未入力・文字列を確認", IF(C7<B7,"終了が開始より前", IF(D7<0,"休憩は0分以上", IF(D7>(C7-B7)*1440,"休憩が拘束時間を超過","")))))

この式では、最初にB〜D列がすべて空欄かを調べます。何かが記入されている行では、3項目が数値になっていることを確かめ、その後に終了と開始の前後関係、負の休憩、拘束時間を超える休憩を順番に調べる構成です。

COUNT関数は、セル範囲のうち数値を含むセルを数え、範囲内の空欄や文字列は数えません。(Microsoft サポート) そのため、この自作例では「日時2項目と分数1項目がそろっているか」の確認に使っています。

ただし、数値であることは、実際の勤務内容が正しいことを意味しません。開始・終了をそろって別の日にしても、前後関係が正しければ警告は出ません。また、勤務があった日の行を丸ごと記入し忘れた場合も、未使用行と区別できない点に注意してください。

手順2：E列で休憩時間を差し引く

E7には、配布ファイルと同じ次の式を設定します。

=IF(COUNTA(B7:D7)=0,"",IF(G7<>"","",C7-B7-D7/1440))

式の中心は、最後の C7-B7-D7/1440 です。C7からB7を引いて拘束時間を求め、D7の分数を時間値に換算して差し引きます。

外側のIF関数は、結果を見せる条件を整えるためのものです。B〜D列がすべて空なら空欄、G列に警告があるときも空欄にします。必要な値がそろい、警告がなくなった行だけが計算対象です。

したがって、E列が空欄でも、直ちに「勤務なし」とは判断できません。B〜D列に記入があるのに結果が出ていないときは、まず同じ行のG列を見てください。E列へ直接「8:00」などを打ち込んで埋めると、その行だけ計算とのつながりが切れてしまいます。

手順3：F列で小数時間、A列で開始日を表示する

F7には、E7が数値のときだけ24を掛ける式を入れます。

=IF(ISNUMBER(E7),E7*24,"")

F列の表示形式は数値にし、たとえば小数点以下2桁でそろえます。E列が 6:45 の行なら、F列は 6.75 です。ここでは見た目をそろえるために、元の式へ一律の切り捨て処理を追加しないようにしましょう。

A7の日付は、B7を基準に求めます。自作例は次のとおりです。

=IF(ISNUMBER(B7),INT(B7),"")

この構成では、A列へ別の日付を手入力しません。A列とB列に同じ情報を二重に持たせると、開始日時だけを直した際に不一致が生じるためです。

A列は日付、B・C列は日付と時刻、D列は分数、E列は経過時間、F列は小数時間という役割を維持してください。すべての列を一括して同じ書式にしないことが大切です。

手順4：37行目まで数式をコピーする

7行目ができたら、A7とE7:G7の数式をそれぞれ37行目までコピーします。B〜D列は記入欄なので、明細の内容まで同じものを複製しないようにしてください。

確認する場所は、途中の行と最終行です。たとえばE8ではB8・C8・D8・G8、E37ではB37・C37・D37・G37を参照する形になっているかを見ます。行ごとの式で $B$7 のように参照先を固定すると、コピー先でも7行目を見続けてしまいます。

担当者が変わっても使えるよう、入力欄と数式欄は色を分けておきましょう。表の上部に「編集するのはB〜D列」「休憩は分数」と短く記載するだけでも、操作の判断基準になります。

翌月用に複製するときは、明細行全体を削除するのではなく、原本を残したうえでB〜D列の内容を消します。数式まで消した空の表を、翌月のひな型にしないことが重要です。

夜勤は開始・終了の両方に日付を入れる

夜勤でつまずく原因の一つは、「22:00」と「7:00」だけで翌日までの経過を表そうとすることです。本稿の方式では、終了日時に翌日の日付を含めます。

配布ファイルも日時方式です。別のサイトで見つけた時刻専用の数式へ、一部の行だけ置き換えないでください。

22時から翌7時は翌日の日付で引き算する

架空例の夜勤は、開始が 2026/9/2 22:00、終了が 2026/9/3 7:00 です。開始から終了まで9時間あり、60分を差し引いて8時間になります。

この行で終了を 2026/9/2 7:00 とすると、終了が開始より前になり、G列で確認を求めます。翌朝の勤務を表すためには、時刻だけでなく日付も翌日にする必要があります。

一方、誤って9月4日の7時を入れた場合は、終了が開始より後なので、単純な前後関係の確認では止まりません。結果が想定より長いときは、まず開始・終了の年月日を読み直してください。

日時方式は経過をそのまま表せますが、異常に長い勤務の妥当性まで判定する仕組みではありません。正の値が出たことだけを根拠に、明細を確定しないようにしましょう。

図を拡大して見る

時刻だけのMOD関数は別方式として使う

日付を持たない既存の表では、MOD関数を使う方法もあります。MOD関数は、数値を指定した除数で割った余りを返す関数です。(Microsoft サポート) この性質を使い、時刻の差を1日未満の範囲に収めるのが次の方法です。

=MOD(C7-B7,1)-D7/1440

ただし、使えるのは、開始・終了が正しく記入され、開始から終了までが24時間未満であると分かっている場合に限ります。休憩の分数や未入力についても、別途確認が必要です。

たとえば、18時から同日の9時という誤入力でも、翌9時までの勤務として15時間の差に置き換わります。本来は確認したい負の差が、もっともらしい正の結果に変わるわけです。

また、開始・終了が同じ時刻では、0時間と24時間を区別できません。日時を入れた値にこの方法を使うと、24時間以上の部分が失われる場合もあります。

本稿のG列は「終了日時が開始日時より前」を警告する設計です。時刻だけの夜勤ではその条件自体が合わなくなるため、配布ファイルのE列だけをMOD関数へ差し替える使い方は避けてください。

月またぎは月次集計の基準を決める

9月30日22時から10月1日7時までのような勤務は、経過時間を求められても、「どちらの月へ何時間を入れるか」が別の問題になります。

社内の予定集計では、開始日が属する月へまとめる方法と、暦日ごとに分ける方法があります。どちらを採るかは集計の目的に合わせ、職場で統一してください。これは、給与計算や法令上の取扱いを自由に選べるという意味ではありません。

配布ファイルのA列は開始日を基準とし、月末で勤務を自動分割する機能はありません。また、E4は明細範囲の合計なので、対象月以外の行を記入しても自動で除外されるわけではありません。

日別に分けたい場合は、深夜0時の前後で明細を分割し、休憩がどちら側に何分あったかも確認します。休憩の合計分数しか分からない状態では、各日の差し引き分を正確に割り振れません。月別ファイルへ移す際は、二重計上と計上漏れの両方を確認しましょう。

月間合計を24時間以上でも正しく表示する

1日ごとの結果が正しくても、合計欄の設定によっては月間の値が短く見えます。ここでは「異常行を除いたまま合計を確定させないこと」と「24時間を超える書式」をセットで整えます。

E4は異常行があるときに合計を止める

単に =SUM(E7:E37) とすると、警告によってE列が空欄になった行を含めずに、残りの値だけが合計されます。その数字を完成した月間合計と取り違えないよう、G列に警告があれば「入力を確認」とする構成にします。

自作する場合のE4の式は、次のとおりです。

=IF(COUNTIF(G7:G37,"?*")>0,"入力を確認",SUM(E7:E37))

COUNTIF関数では、条件に使う ? が任意の1文字、* が任意の一連の文字列に対応します。(Microsoft サポート) この自作例では "?*" を使い、1文字以上の警告文があるかを調べています。

E4が「入力を確認」になったら、G7:G37を確認し、警告のある行を修正してください。E4へ手計算の合計を上書きするのではなく、原因となった明細を直すのが基本です。

ただし、明細がすべて空なら合計は0になります。この場合の0は「全日分の確認が終わり、勤務がなかった」という判定ではありません。勤務日を丸ごと記入し忘れたケースは、元の予定表や実績明細との照合で補います。

[h]:mmとh:mmの違いを確認する

24時間を超える経過時間の合計には、時間部分を角括弧で囲んだ表示形式を使います。Microsoftも、24時間以上の合計を示すために [h]:mm;@ の設定を案内しています。(Microsoft サポート) 本稿では、E列とE4に [h]:mm を指定します。

同じ31時間45分の値でも、見え方は次のように変わります。

設定するときは、E7:E37とE4を選び、セルの書式設定でユーザー定義の表示形式を開き、[h]:mm を指定します。画面上の名称は利用環境で異なる場合があるため、最終的には設定されたコードを確認してください。

合計が7:45に見えていても、元の値から24時間が消えたとは限りません。最初に確認するのはSUM関数ではなく書式です。見える数字に合わせて手動で24時間を足すと、元の値を二重に補正してしまいます。

図を拡大して見る

TEXT関数は見せるための別セルで使う

TEXT関数は数値を文字列へ変換します。後の計算には、TEXT関数の結果ではなく、別に保持した元の数値を参照するのがMicrosoftの案内です。(Microsoft サポート) そのため、E列をTEXT関数へ置き換えるのではなく、必要なら説明用の別セルを設けます。

たとえば、E4が数値のときだけ合計の文言を作る自作例は、次のとおりです。

=IF(ISNUMBER(E4),"合計 "&TEXT(E4,"[h]:mm"),E4)

このセルは、帳票上で「合計 31:45」と見せるためのものです。別の集計へ渡す値は、引き続きE4を使います。

時間と分で読むE列、小数時間で使うF列、文章として見せる補助欄を分ければ、目的に合わせて見た目を変えても計算元を保てます。合計が出なくなった際も、元の数値と文字列のどちらを参照しているかを確認できます。

答えが合わないときは入力値と参照先を調べる

結果が合わないときに、最初から複雑な関数へ変更する必要はありません。まず「元の値」「単位」「参照先」「書式」の順番で調べてください。

特に、表示されている値だけを見て正誤を判断すると、日付の省略や小数時間との混同を見逃します。異常のある1行に絞り、B列からG列まで順に確認するほうが原因を追えます。

文字列・単位違い・表示崩れを切り分ける

本稿の構成で先に確認したい点を、症状別にまとめます。

D列は特に注意が必要です。1時間のつもりで 1 と入れても、この表では1分です。また、1:00 という時刻形式を入れる方法も、本稿の分数欄とは合いません。

このような単位違いは、数値として受け付けられればG列で止まらないことがあります。「エラーが出ていないから正しい」と判断せず、D列の見出しを「休憩（分）」のまま残してください。

B・C列も同様です。時刻だけを記入しても数値として扱われる場合があるため、数値判定だけで日付の省略を防ぐことはできません。最初の記入例と照らし、年月日までそろっているかを確認します。

行追加とコピー後に正常・異常の両方を試す

1日に勤務を分けて複数行にするなど、31行では足りない場合は、行数を増やすだけで終わらせないでください。追加行のA・E・F・G列に数式があることと、E4の合計範囲・警告確認範囲の両方が追加行まで届くことを確かめます。

たとえば45行目まで使うなら、E4の式では G7:G45 と E7:E45 の両方を対象にします。片方だけを広げると、追加分が合計から漏れたり、追加行の警告を無視したりする原因になります。

変更後は、通常の例だけでなく、意図的に不備を作った例も試してください。

• B〜D列をすべて空欄にした行は、E・F・G列も空欄になるか。
• D列だけを空欄にすると、警告が出てE4が「入力を確認」になるか。
• 終了を開始より前にすると、結果の確定を止められるか。
• 休憩を負の数や拘束時間超過にすると、警告が出るか。
• 4行の架空例へ戻すと、合計が31:45になるか。

このチェックは、警告を出す仕組みが残っているかを確かめるためのものです。試験後は不備を作った値を戻し、修正前の原本と区別して保存します。数式の変更者と変更内容を短く残しておけば、翌月以降に問題が出たときも修正箇所をたどれます。

勤怠管理に使う前に法定休憩と実績を確認する

ここまでで確認しているのは、主に入力内容と計算の整合性です。勤怠管理に使うには、実際の労働時間や休憩の扱いも確認しなければなりません。

配布ファイルには、法定休憩の自動判定、残業・深夜割増の判定、給与計算の機能はありません。結果が数値になったことと、労務上の確認が終わったことを分けて扱いましょう。

法定休憩は入力エラーの有無とは別に判断する

2026年9月10日時点の厚生労働省の案内では、労働基準法第34条により、労働時間が6時間を超えて8時間以下の場合は少なくとも45分、8時間を超える場合は少なくとも1時間の休憩を与える必要があります。(厚生労働省)

判断に使うのは、単に開始から終了までの拘束時間ではありません。表に記入した休憩と、実際に労働した時間を区別して確認します。

架空例の9月5日は、9時から18時まで、休憩0分としているため、計算結果は9時間です。数値の前後関係に矛盾がないので、入力確認では通る構成ですが、通常の法定休憩の原則を満たす勤務例としては扱えません。

警告が出ないことを法令適合の印にしないでください。予定作成時には必要な休憩を含めて勤務を組み、実績を扱う際には、予定の休憩時間を機械的に差し引くのではなく、実際の内容を確認します。

なお、この表は休憩の合計分数を扱うもので、取得した時刻までは管理しません。休憩の配置や実施状況を確認する必要がある職場では、その情報を別途残す必要があります。

分単位の実績を残し、給与計算を分離する

時刻を分単位で扱う表では、元の始業・終業を残したまま計算してください。数字を整えるために、最初から15分などの区切りで切り捨てる式を入れないことが重要です。

2026年9月10日時点の静岡労働局の案内では、1日ごとに一定時間未満の労働時間を一律に切り捨て、その分の賃金を支払わないことは労働基準法違反とされています。(都道府県労働局)

小数時間の表示桁数にも注意しましょう。たとえば7時間59分は約7.9833時間であり、小数点以下2桁の見た目では7.98になります。この見た目の数字を別の表へ手入力すると、元の分数とはずれが生じます。

別の集計へ渡すときは、表示を見て打ち直すより、元の数値を使う構成を検討してください。丸めが必要な処理は、対象となる項目と根拠を確認したうえで、元データとは別の段階に設けます。

また、F列に時給を掛けるだけで、実際の給与計算が完成するわけではありません。残業や深夜の扱いなどが必要なら、この表で確認した実績を基に、職場の制度に対応した勤怠システムや給与計算の工程へ引き継ぎます。

自動化の範囲を分けて運用する

この表で自動になるのは、記入済みの開始・終了・休憩から結果を求める処理と、定めた条件に対する入力確認です。誰を何日に配置するかは、担当者が決めて記入します。

また、従業員全員の明細を一つのファイルへ自動で取りまとめる機能もありません。複数人に展開する場合は、各人の原本、対象期間、修正担当、確定版の保存先をそろえ、別の人の値を混ぜない運用を先に決めましょう。

計算だけで足りるか、割り当ても必要かを判断する

日々の引き算や月間合計が主な課題なら、まずこの構成で足りるかを試せます。無料・有料を問わず、テンプレート選びでは見た目だけでなく、夜勤、未入力、異常行、24時間超の合計をどう扱うかを確認してください。

一方、シフト希望と必要人数を照合し、誰を配置するかを考える作業が負担なら、勤務時間の集計とは別に、割り当てを支援する仕組みを検討する余地があります。

シフトラでは、2026-09-05時点の機能として、自然な日本語で伝えたルールやシフト希望・必要人数を踏まえたシフト案の生成と、確定シフトのExcel出力に対応しています。(シフトラ)

生成結果はあくまで案であり、最終確定と労務上の判断は事業者が行います。打刻や給与計算そのものを行うサービスでもないため、勤務予定を組む工程と、実績を確認する工程を分けて考えてください。

まずは4行の架空例で計算結果を確かめ、入力欄・数式欄・合計範囲を整えましょう。そのうえで、勤務内容の確認に集中できる運用を作ることが、エクセルのシフト表を実務で使い続けるための基本です。