Excel のシフト表で、深夜と休日の時間を分けて数える式｜日をまたぐ勤務にも対応

お店のシフト表を Excel で作っていると、月末の集計で手が止まることがあります。「22時を過ぎた分は深夜で数える」「土日と祝日の分は別に数える」「閉店作業で日をまたいだ人がいる」。電卓で1人ずつ数えていると、時間もかかり、数え間違いも起きやすくなります。

この記事では、1日1行のシフト表に式を入れて、勤務時間・深夜の時間・休日の時間を分けて数える方法を紹介します。例はすべて架空のデータです。

1. 表の形をそろえる

まず、1回の勤務を1行にして、次の4つを入れる列を作ります。

・A列：日付（例：2026/11/7）

・B列：開始（例：17:00）

・C列：終了（例：22:30）

・D列：休憩（時間の数。30分なら 0.5）

開始と終了は「17:00」のように、時刻として入れるのがポイントです。「17時」「17.5」のように書き方が混ざると、式が正しく動きません。

2. 日をまたぐ終了時刻を直す

22:00 から翌朝 5:00 まで働いた場合、そのまま「5:00 − 22:00」を計算すると、マイナスになってしまいます。そこで、終了が開始より前なら「翌日の終了」として扱う列（E列）を作ります。

E列：=C2+(C2<B2)

終了が開始より前のときだけ1日（24時間）を足す式です。これで 22:00〜5:00 は7時間として数えられます。

3. 勤務時間を「時間の数」にする

Excel の時刻は「1日＝1」という数で記録されています。そのため、差に24をかけると「時間の数」になります。

F列（拘束時間）：=ROUND((E2-B2)*24,2)

G列（勤務時間）：=F2-D2

ROUND は、計算の途中でできる小さな誤差（8.0000001 のような数）を消すために入れています。時間の数にしておくと、あとで時給をかけるのも簡単です。

4. 深夜（22時〜翌5時）の時間を数える

深夜の時間は、「22時から翌5時まで」と「その日の0時から5時まで」の2つの枠に、勤務がどれだけ重なっているかを足して求めます。

H列（深夜・休憩前）：=ROUND((MAX(0,MIN(E2,29/24)-MAX(B2,22/24))+MAX(0,MIN(E2,5/24)-B2))*24,2)

29/24 は「翌日の5時」、22/24 は「22時」、5/24 は「5時」のことです。重なりがないときは MAX(0, …) で0になります。この式で、18:00〜23:00 なら1時間、22:00〜5:00 なら7時間、早朝の 3:00〜9:00 なら2時間と数えられます。

休憩を深夜の時間帯にとることもあります。ここでは「休憩は、まず深夜以外の時間からとる」と決めて、深夜以外の時間で引ききれない分だけ深夜から引きます。

I列（深夜）：=MAX(0,H2-MAX(0,D2-(F2-H2)))

22:00〜5:00 で休憩1時間なら、深夜以外の時間がないので、深夜は6時間になります。休憩の扱いはお店によって違うので、決まりに合わせて変えてください。

5. 休日の時間を分ける

別のシートに祝日の一覧を作っておき（例：「祝日」シートのA列）、土日か一覧にある日なら「休日」と判定します。

J列（休日？）：=OR(WEEKDAY(A2,2)>=6,COUNTIF(祝日!$A:$A,A2)>0)

K列（休日の時間）：=IF(J2,G2,0)

L列（平日の時間）：=G2-K2

WEEKDAY の2つ目に2を入れると、月曜が1、土曜が6、日曜が7になります。お店の定休日や、休日として数える日がちがう場合は、ここを変えます。

なお、この式は日をまたぐ勤務を「始まった日」で判定する簡単な形です。どの日を休日として割増するか（法律で決まった休日かどうか）は、お店の就業規則に合わせてください。深夜の割増（25%以上）とちがい、休日の割増は対象になる日が決まりによって変わります。

6. 月の合計を出す

最後に、G・I・K・L列をそれぞれ SUM で合計すれば、1人分の月の勤務時間・深夜の時間・休日の時間・平日の時間がわかります。架空の7回分の勤務で試したところ、勤務48.5時間のうち、深夜10時間・休日19時間・平日29.5時間と分けて数えられました。

スタッフが何人もいるときは、名前の列を足して SUMIFS で人ごとに合計するか、1人1シートにまとめると見やすくなります。

まとめ

時刻は時刻として入れる、日をまたぐ終了には1日を足す、24をかけて時間の数にする、深夜は2つの枠との重なりで数える、休日は祝日の一覧で判定する。この5つをおさえておくと、月末の集計がぐっと楽になります。

お店に合わせて作ることもできます

ココナラで、飲食店・小売店向けに、お店のシフトの種類や休憩の決まりに合わせたシフト表・勤怠集計の Excel を作るサービスを出品しています。今のシフト表の写真を見ながら、「この店の決まりだとどう数えるか」のご相談から受け付けています。すぐに使える形で十分な場合は、深夜・残業・休憩を自動で計算するテンプレート（コンテンツマーケット）もあります。