エクセルでシフトの深夜時間を計算するには、勤務時間と22時〜翌5時の重なりを取り出し、深夜帯に取った休憩だけを引きます。日付をまたぐ終了時刻を翌日側にそろえ、早朝に始まる勤務も含めて計算するのがポイントです。
今回紹介する配布Excel(無料のテンプレート)では、開始・終了・深夜帯の休憩を入力すると、休憩前と休憩後の深夜時間が自動表示されます。この記事では、式のしくみと確認の方法を解説します。シフトを作成する担当者が勤務時間を確認するための表で、勤務の自動割当は行いません。
配布Excelに開始・終了・深夜帯の休憩を入力する
記入例と確認例を入れたExcelを無料でダウンロードできます。
このページの配布ファイルで、登録は不要です。
ブックは「使い方」「設定」「計算」「確認例」の構成で、マクロは使っていません。黄色が入力欄、灰色が数式のセルです。操作は簡単で、次の順に進めます。
- 「設定」のB3が22:00、B4が5:00、B5が「OK」になっているか確認します。
- 「計算」のC列に開始、D列に終了を時刻で入力します。1勤務につき1行で、2〜201行の200勤務まで使えます。
- E列には、休憩のうち深夜帯に取った分だけを数字で入れます。休憩がなければ空欄のままで構いません。
- L列の確認を読み、J列の「深夜帯にかかる時間」と、K列の「深夜時間」を確認します。
A列の勤務日とB列の氏名は任意です。以下は、架空の24時間営業の店舗で、2026年12月1日(火)〜12月3日(木)に始まる8勤務の記入例。氏名・勤務・休憩はすべて架空で、配布ファイルと同じ値です。
| 勤務日(A) | 氏名(B) | 開始(C) | 終了(D) | 深夜帯の休憩・分(E) | 休憩前・h(J) | 深夜時間・h(K) |
|---|---|---|---|---|---|---|
| 2026-12-01 | 佐藤 | 17:00 | 22:00 | 空欄 | 0 | 0 |
| 2026-12-01 | 鈴木 | 22:00 | 5:00 | 60 | 7 | 6 |
| 2026-12-01 | 高橋 | 21:00 | 6:00 | 60 | 7 | 6 |
| 2026-12-02 | 田中 | 4:00 | 9:00 | 空欄 | 1 | 1 |
| 2026-12-02 | 伊藤 | 18:00 | 25:00 | 空欄 | 3 | 3 |
| 2026-12-02 | 渡辺 | 23:00 | 8:00 | 45 | 6 | 5.25 |
| 2026-12-03 | 山本 | 9:00 | 18:00 | 空欄 | 0 | 0 |
| 2026-12-03 | 中村 | 16:30 | 9:30 | 120 | 7 | 5 |
この例は全行の確認が「OK」で、深夜時間の合計は26.25時間です。集計欄は次のようになります。
| セル | 内容 | 数式 | 記入例の結果 |
|---|---|---|---|
| O2 | 勤務の件数 | =COUNTIF(L2:L201,"OK")+O3 | 8 |
| O3 | 要確認の件数 | =SUMPRODUCT((LEFT(L2:L201,3)="要確認")*1) | 0 |
| O4 | 深夜時間の合計(h) | =SUM(K2:K201) | 26.25 |
| O5 | 休憩前の深夜帯の合計(h) | =SUM(J2:J201) | 31 |
合計を見る前にO3を確認してください。「要確認」がある行はI・J・K列が空欄になり、時間の合計に入りません。数字が表示されていても、確認が残っていれば全勤務の集計は終わっていない状態です。
日またぎの時刻を計算できる形にそろえる
22:00〜5:00を同じ日の時刻として引き算すると、終了より開始のほうが大きいためマイナスになります。また、日付をまたいだ勤務で終了から開始を引けても、それだけでは深夜の時間帯にかかる部分を分けられません。
Excelでは、時刻を0以上1未満の小数で表します。そこで配布Excelでは、時刻に1440を掛けて分に直してから計算しています。(Microsoft サポート「TIME 関数」)
C・D列の表示形式は[h]:mmです。終了を25:00と入力することも可能で、翌日1:00までの勤務としてそのまま使えます。開始は勤務を始めた日の0:00〜23:59で入れ、24:00以上にするのは終了だけにしてください。
| セル | 数式 |
|---|---|
| 計算のF2 | =IF(ISNUMBER(C2),ROUND(C2*1440,0),"") |
| 計算のG2 | =IF(AND(ISNUMBER(C2),ISNUMBER(D2)),ROUND(D2*1440,0)+IF(ROUND(D2*1440,0)<=ROUND(C2*1440,0),1440,0),"") |
F列は開始の分、G列は終了の分です。終了が開始以下なら、G列で1440分を加算して翌日側へ送ります。25:00のように既に翌日側の値が入っていれば、重ねて加算しません。
ROUNDは指定した桁数に四捨五入する関数で、桁数を0にすると整数に丸めます。上の式は、時刻を分の整数にして小数の端数を抑えるためのものです。(Microsoft サポート「ROUND 関数」)
秒を含む入力も分に丸める設計です。労働時間の端数処理を決めるための式としては使わず、分単位で整理した勤務を入力してください。
MAX・MINで深夜帯との重なりを求め、休憩を引く
MAX関数は指定した数値の最大値、MIN関数は最小値を返します。(Microsoft サポート「MAX 関数」)(Microsoft サポート「MIN 関数」)
重なりを求める基本的な方法は、「終了と帯の終わりの早いほう」から「開始と帯の始まりの遅いほう」を引くことです。重ならないと引き算が負になるため、最後に0と比較して大きいほうを残します。
配布Excelでは、次の3つの帯との重なりを足しています。分の数値は勤務開始日の0時を基準にしたものです。
| 帯 | 配布時の時間帯 | 分で表した範囲 |
|---|---|---|
| 当日の早朝 | 当日0:00〜5:00 | 0〜300 |
| 当日の夜から翌朝 | 当日22:00〜翌5:00 | 1320〜1740 |
| 翌日の夜から翌々朝 | 翌日22:00〜翌々日5:00 | 2760〜3180 |
最初の帯があるので、田中の4:00〜9:00も深夜1時間として拾えます。3つ目は、確認例の23:30〜翌23:00のように、翌日の22時以降まで続く勤務のために必要です。開始は当日内、1行は24時間未満という条件なので、この3つの帯で対象を覆えます。
計算のH2
=IF(AND(設定!$B$5="OK",ISNUMBER(F2),ISNUMBER(G2)),IF(AND(F2>=0,F2<1440,G2-F2<1440,D2>=0),MAX(0,MIN(G2,設定!$B$8)-MAX(F2,0))+MAX(0,MIN(G2,1440+設定!$B$8)-MAX(F2,設定!$B$7))+MAX(0,MIN(G2,2880+設定!$B$8)-MAX(F2,1440+設定!$B$7)),""),"")
H列で算出した分を時間に直すのがJ列、そこから深夜帯の休憩を引くのがK列です。I列は開始から終了までの経過時間で、休憩を控除した実労働時間ではありません。
| セル | 数式 |
|---|---|
| 計算のI2 | =IF(L2="OK",(G2-F2)/60,"") |
| 計算のJ2 | =IF(L2="OK",H2/60,"") |
| 計算のK2 | =IF(L2="OK",(H2-N(E2))/60,"") |
E列に入れるのは勤務全体の休憩ではありません。深夜帯に取る休憩は人によって違うため、1行ずつ入力します。休憩の時刻を確認し、設定した深夜帯の中に入った分だけを数えて入力します。複数回の休憩があれば、そのうち深夜帯に入った分を合計してください。休憩の開始・終了から自動で切り分ける処理は入れていません(式が複雑になるためです)。
鈴木の22:00〜5:00では、深夜帯の7時間から休憩60分を引いて6時間です。渡辺の23:00〜8:00は、深夜帯360分から休憩45分を引いた315分、つまり5.25時間になります。
深夜帯の設定を変え、確認例20件で検算する
深夜帯を変えるときは、「設定」のB3に開始、B4に終了を入れます。対象は、開始の時刻が終了より後になる、日付をまたぐ帯1つです。5:00〜22:00のような日中の帯や、複数の帯の指定には対応していません。
B5で設定を確認し、B7・B8で開始と終了を分に直しています。
| セル | 数式 |
|---|---|
| 設定のB5 | =IF(AND(ISNUMBER(B3),ISNUMBER(B4)),IF(AND(B3>B4,B3<1,B4>=0),"OK","要確認:深夜帯は開始を終了より後の時刻にします(例 22:00〜5:00)"),"要確認:深夜帯の開始と終了は時刻で入力します") |
| 設定のB7 | =IF(B5="OK",ROUND(B3*1440,0),"") |
| 設定のB8 | =IF(B5="OK",ROUND(B4*1440,0),"") |
配布時はB5が「OK」、B7が1320、B8が300です。B5が「OK」なら設定に問題はありません。「要確認」になったら、計算を進める前にB3・B4を直してください。
「確認例」シートには、時間が出る15件と確認表示が出る5件があります。C〜E列が入力、F〜L列が計算、M列が手計算の値、N列が一致の判定、O列が手計算の内訳です。以下は22:00〜5:00を設定したときの値で、休憩の空欄は0分を表します。終了が開始以下の行は翌日の終了です。
| No. | 開始〜終了 | 深夜帯の休憩(分) | 深夜時間(h) | 手計算の内訳 |
|---|---|---|---|---|
| 1 | 22:00〜5:00 | 空欄 | 7 | 22:00〜翌5:00の7時間すべて |
| 2 | 21:00〜6:00 | 空欄 | 7 | 21:00〜22:00と5:00〜6:00を除く7時間 |
| 3 | 23:00〜2:00 | 空欄 | 3 | 23:00〜翌2:00の3時間 |
| 4 | 4:00〜9:00 | 空欄 | 1 | 4:00〜5:00の1時間 |
| 5 | 17:00〜22:00 | 空欄 | 0 | 深夜帯にかからない |
| 6 | 22:00〜23:30 | 空欄 | 1.5 | 22:00〜23:30の1.5時間 |
| 7 | 18:00〜5:00 | 空欄 | 7 | 22:00〜翌5:00の7時間 |
| 8 | 0:00〜8:30 | 空欄 | 5 | 0:00〜5:00の5時間 |
| 9 | 16:30〜9:30 | 60 | 6 | 22:00〜翌5:00の7時間から休憩1時間を引く |
| 10 | 9:00〜18:00 | 空欄 | 0 | 深夜帯にかからない |
| 11 | 18:00〜25:00 | 空欄 | 3 | 22:00〜25:00(翌1:00)の3時間 |
| 12 | 23:30〜23:00 | 空欄 | 6.5 | 23:30〜翌5:00の5.5時間と、翌日22:00〜23:00の1時間 |
| 13 | 5:00〜22:00 | 空欄 | 0 | 深夜帯にかからない |
| 14 | 3:30〜5:15 | 15 | 1.25 | 3:30〜5:00の90分から15分を引いた75分 |
| 15 | 22:15〜4:45 | 空欄 | 6.5 | 22:15〜翌4:45の6.5時間 |
次の5件は時間を出さず、L列の確認表示とM列に用意した文字列が一致するかを確かめます。
| No. | 入力 | 出るはずの確認 |
|---|---|---|
| 16 | 開始26:00・終了30:00 | 要確認:開始が24:00以上 |
| 17 | 開始9:00・終了9:00 | 要確認:24時間以上の勤務 |
| 18 | 23:00〜2:00・休憩200分 | 要確認:深夜帯の休憩が深夜帯にかかる時間より長い |
| 19 | 開始が文字列の「22:00」・終了5:00 | 要確認:時刻が文字列 |
| 20 | 開始22:00・終了空欄 | 要確認:開始か終了が空欄 |
数式の結果と手計算の値を比較するN2の式は次のとおりです。M列は固定の値であり、設定を変えても自動では書き換わりません。
確認例のN2
=IF(A2="","",IF(L2="OK",IF(ISNUMBER(M2),IF(ROUND(K2*60,0)=ROUND(M2*60,0),"一致","不一致"),"不一致"),IF(L2=M2,"一致","不一致")))
| セル | 数式 | 配布時の結果 |
|---|---|---|
| 確認例のR3 | =COUNTIF(N2:N21,"一致") | 20 |
| 確認例のR4 | =COUNT(A2:A21) | 20 |
| 確認例のR5 | =IF(R3=R4,"すべて一致","不一致あり") | すべて一致 |
利用前にN列がすべて「一致」、R5が「すべて一致」になり、式が正しい結果を返すことを確かめます。確認例は、設定を変えたときの検算にも使える便利なシートです。自分の職場でよくあるパターンの勤務で試すときは、C〜E列を書き換え、M列の手計算の値も合わせて更新してください。
配布時の休憩の入力をそのままにして設定を23:00〜6:00へ変えると、記入例の合計は26.25時間のままでも、確認例は20件中11件の一致になります。時間が変わる9件のM列を直さないと「不一致」が残るため、合計だけで設定変更の結果を判断しないでください。
実際の運用で帯を変更した際は、E列の休憩も、変更後の時間帯に入った分になっているか数え直します。
答えが合わないときはL列の確認から原因を探す
L列では、空欄、文字列、勤務の長さ、休憩の入力を順に確認します。ISNUMBERは数値ならTRUEを返し、文字列の数字を数値へ変換して判定することはありません。見た目が時刻でも、文字列として保存されていると確認の対象になります。(Microsoft サポート「IS 関数」)
計算のL2
=IF(AND(C2="",D2=""),"",IF(設定!$B$5<>"OK","要確認:設定シートの深夜帯を確認",IF(OR(C2="",D2=""),"要確認:開始か終了が空欄",IF(OR(NOT(ISNUMBER(C2)),NOT(ISNUMBER(D2))),"要確認:時刻が文字列",IF(OR(C2<0,D2<0),"要確認:時刻がマイナス",IF(F2>=1440,"要確認:開始が24:00以上",IF(G2-F2>=1440,"要確認:24時間以上の勤務",IF(AND(E2<>"",NOT(ISNUMBER(E2))),"要確認:深夜帯の休憩は分の数字で入力",IF(N(E2)<0,"要確認:深夜帯の休憩は分の数字で入力",IF(N(E2)>H2,"要確認:深夜帯の休憩が深夜帯にかかる時間より長い","OK"))))))))))
表示ごとの対策は、次の一覧の通りです。
| 確認の内容 | 入力例と直し方 |
|---|---|
| 時刻が文字列 | 鈴木のC3が文字列の「22:00」なら、書式を時刻にして22:00と入力し直す。書式の変更だけで済ませない |
| 開始が24:00以上 | 伊藤のC6を26:00にすると対象外。開始は勤務開始日の時刻で入力する |
| 24時間以上の勤務 | 山本のD8を開始と同じ9:00にすると翌日の9:00扱い。終了を確かめる |
| 深夜帯の休憩が長すぎる | 渡辺のE7が400だと、深夜帯にかかる360分を超えている。深夜帯に取った分だけに直す |
| 休憩が数字でない・マイナス | 高橋のE4が「60分」や-10なら、0以上の分の数字で入れ直す |
| 開始か終了が空欄 | 田中のD5が空欄なら、終了を入力する |
| 時刻がマイナス | 時刻を入力し直す |
| 設定シートの深夜帯を確認 | B3・B4が時刻か、開始が終了より後かを確認する。「22時」のような文字列は使わない |
鈴木の開始が文字列になった例では、要確認が1件、深夜時間の合計が20.25時間になります。鈴木の時間が短くなったのではなく、その行が合計から外れた結果です。
なお、C・D列が両方とも空白なら未使用の行として扱い、L列も空欄になります。L列の「OK」はこのブックの入力条件を満たすという意味で、勤務の適法性を判定した表示ではありません。
使いどころと限界、始める前に決めておくこと
この表は、入力した勤務から特定の深夜帯だけを切り出すためのもので、通常の勤務時間の集計とは別に使います。残業(時間外労働)や休日労働の時間の切り分け、残業時間の集計、時給と割増の率を使う手当などの金額の計算は行いません。深夜として扱う時間帯や賃金の扱いが、法令・就業規則に合っているかの判定にも使えません。
2026-11-13時点で、厚生労働省は、労働基準法第37条第4項の説明で深夜の時間帯を午後10時から午前5時までとしています。配布時の22:00〜5:00はこの時間帯に合わせた設定です。(厚生労働省「よくある質問(深夜は割増になる)」)
設定の変更は集計対象を変える操作で、法令上の区分を変えるものではありません。会社のルールで、22時〜5時以外の時間帯を別に集計したい場合などに使います。職場の規程と法令を確認したうえで、何の時間を求める表なのかを共有してください。
シフト管理で使い始める前には、入力するのが勤務予定か実績かを決めます。予定を入れれば予定の深夜時間が出ます。実際の出勤・退勤を使う場合も、勤怠システムの記録や休憩の情報を担当者が整理する必要があり、打刻データとの突合はこのブックの対象外です(エクセルで夜勤の打刻を突合する方法で扱っています)。
あわせて、休憩を誰が記録し、深夜帯に入った分を誰が数え、L列の確認を誰が直すかを決めておきましょう。全体の休憩時間しか分からない状態ではE列を正確に埋められません。入力と確認の業務を分けておくと、変更が発生した際にも元の記録に戻って修正しやすくなります。深夜にかかる勤務が多い職場では、月の途中でも一度合計を確かめておくと、入力の誤りを早く見つけられます。
シフトの深夜時間の計算でよくある質問
24時間以上の勤務はどう扱いますか?
1行では計算できません。入力上は24時間未満の区間に分け、それぞれの開始・終了と、その区間の深夜帯に取った休憩を入れます。元の勤務との対応は担当者が管理してください。分けて入力しても、勤務そのものが適切かどうかを確認する機能はありません。
200勤務を超える場合は行を追加できますか?
多くの勤務をまとめて扱う場合は、F〜L列の数式を下へコピーし、O2〜O5にある2〜201行の参照範囲も広げます。入力する行だけを増やすと、計算されなかったり合計から漏れたりするため、数式と集計範囲を一緒に変更してください。
勤務日や氏名を変えると計算結果も変わりますか?
A列の勤務日とB列の氏名は計算に使いません。日またぎはC・D列の時刻から判断します。勤務日を変えただけでは、終了が翌日扱いになる条件は変わりません。
小数で表示された時間はどう読みますか?
時間は小数で表示しており、6.5は6時間30分です。J・K列の値は「時:分」の時刻ではないので、C・D列と同じ表示形式に変更せず、時間数として読み取ってください。
Excelのどのバージョンで検算されていますか?
配布Excelの検算はLibreOffice 26.2での再計算です。記入例8勤務と確認例20件を照合し、設定変更・入力不備の例も確認していますが、Excel各版での実機確認はしていません。使用する環境で、まず確認例の結果を確かめてください。
深夜時間を求めた後の作業
深夜時間を求めた後は、時給や割増の扱いを確認する給与計算の工程へ引き継ぎます。このブックだけで給与の金額まで確定することはできません。
深夜の割増を含む概算の人件費を日別・人別に集計する方法はシフト表の人件費をエクセルで計算する方法、勤務時間(終了−開始−休憩)の計算はシフト表をエクセルで自動計算する方法、2交代のシフト表は2交代勤務のシフト表を作る手順で扱っています。
シフトラは、深夜の勤務を24時を超える時刻の表記で設定できます。また、本部の設定で有効にすると、Excel出力の予定シフトのチェック用シートに、設定した深夜帯と重なる予定を参考情報として表示します(2026-09-05時点の製品情報。利用条件は設定・権限によります)。給与の計算や実績の勤怠チェック、この記事の配布Excelへの入力を自動で行う機能ではありません。
L列の「要確認」を解消し、入力した勤務と休憩に対応するK列の値とO4の合計を確かめてから、次の作業に活用してください。