エクセルで夜間シフト表をガントチャートにするには、勤務開始・勤務終了・横軸の時刻を、すべて日付を含む「日時」でそろえることが基本です。22:00と7:00だけで比較せず、「9月15日22:00から9月16日7:00」と入力し、条件付き書式で勤務と休憩を塗り分けます。
以下では、20時から翌8時までを30分刻みで表示する夜間シフト表を作ります。表と数式を手元のExcelへコピーして再現できる構成です。自動になるのは入力済みの勤務の色分けであり、誰を勤務に割り当てるかは担当者が決めます。
日付をまたぐ夜間シフトは「日時」でそろえる
夜間シフトでは、開始時間より終了時間の数字が小さくなることがあります。しかし、時刻だけでは「同じ日の朝」なのか「翌日の朝」なのかを区別できません。
次は、同じ勤務を入力する場合の比較です。
| 入力方法 | 勤務開始 | 勤務終了 | 比較する内容 |
|---|---|---|---|
| 時刻だけ | 22:00 | 7:00 | 翌日という情報がない |
| 日付を含む日時 | 2026/9/15 22:00 | 2026/9/16 7:00 | 終了が開始より後になる |
Excelでは、日時の数値を日付や時刻の表示形式で見せられます。見た目だけを時刻表示にしても、元データには日付を含む日時を残す設計にしましょう。(Microsoft サポート)
今回は、MOD関数で翌日を推測するアプローチは使いません。24時間以上の勤務や終了日の誤入力を見落とさないよう、日付を明示して比較します。
手順1：勤務と休憩の入力欄を作る
新しいワークシートを開き、左側に入力欄、右側にガントチャートを配置します。この例は、1行につき勤務1区間・休憩1区間を扱う設計です。
A1:E2に架空の勤務例を入力する
A1:E1に見出し、A2:E2に次のデータを入力してください。Aさんの勤務は、表示を検算するための架空例です。
| 氏名 | 勤務開始 | 勤務終了 | 休憩開始 | 休憩終了 |
|---|---|---|---|---|
| Aさん | 2026/9/15 22:00 | 2026/9/16 7:00 | 2026/9/16 2:00 | 2026/9/16 3:00 |
日付と時刻の間には半角スペースを入れます。休憩開始・休憩終了にも翌日の日付が必要です。F列は空け、G列から右を帯の表示に使います。
最初から全員分を入力するより、まずAさんの1行で色分けを確認し、その後で3行目以降に追加すると、設定の誤りを切り分けられます。
表示形式を変えて翌日の日付を確認する
B列からE列を選択して「Ctrl＋1」を押し、「セルの書式設定」→「表示形式」→「ユーザー定義」の「種類」に、m/d h:mmを設定します。これで月日と時分を一緒に表示できます。(Microsoft サポート)
Aさんの勤務開始が「9/15 22:00」、勤務終了が「9/16 7:00」と見えることを確認してください。文字が収まらなければ列幅を広げます。
表示形式を変える操作と、文字列を日時の数値に直す操作は別です。貼り付けたデータが文字列になっている場合の確認方法は、後半で説明します。
手順2：20時から翌8時の時刻軸を作る
横軸も、勤務データと同じ日付を含む日時で作ります。G1には、2026/9/15 20:00と入力してください。
H1の数式をAE1までコピーする
H1に次の数式を入力し、AE1まで右へコピーします。
=$G$1+(COLUMN()-COLUMN($G$1))/48Excelの時刻は1日の一部を小数で表すため、30分は1日を48等分した値です。COLUMN関数で求めた列番号の差を使い、G1を基準に30分ずつ進めています。(Microsoft サポート) (Microsoft サポート)
コピー後は、G1:AE1にも表示形式m/d h:mmを設定します。確認する目印は、H1が「9/15 20:30」、O1が「9/16 0:00」、AE1が「9/16 8:00」です。
ここでは日付の読み違いを避けるため、横軸も月日付きのまま使います。
描画する24枠と終端を分ける
各列の見出しは、その列が表す時間帯の開始時刻です。G列は20:00以上20:30未満、H列は20:30以上21:00未満を表します。
| セル・範囲 | 役割 |
|---|---|
| G1:AD1 | 20:00から翌7:30までの各枠の開始時刻 |
| AE1 | 最後の枠が終わる翌8:00 |
| G2:AD101 | 勤務と休憩を描画する範囲 |
20時から翌8時までは12時間なので、30分幅なら24枠です。AE1は終端の確認用であり、AE列の下には帯を描きません。
G2:AD101には薄い罫線を設定し、通常の背景を白にしておきます。帯を描くセルには、文字や数式を入力する必要はありません。
手順3：条件付き書式で勤務と休憩を塗り分ける
勤務用と休憩用の2つのルールを作ります。次の操作名は、Microsoftサポート情報をもとにしたWindows版Excelの表記です。版によって項目名が多少異なる場合があります。(Microsoft サポート)
勤務を青く塗るルールを登録する
G2をアクティブセルにした状態で、G2:AD101を選択します。数式バーの左にある名前ボックスへG2:AD101と入力してEnterを押す方法でも選択できます。
「ホーム」→「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」を開き、次の式を登録してください。(Microsoft サポート)
=AND(ISNUMBER($B2),ISNUMBER($C2),$C2>$B2,G$1>=$B2,G$1<$C2)「書式」→「塗りつぶし」で青を選び、確定します。「ルールの管理」で、適用先が=$G$2:$AD$101になっていることも確認しましょう。
AND関数はすべての条件が成立するか、ISNUMBER関数は値が数値かを調べます。(Microsoft サポート) (Microsoft サポート)
この式では、勤務開始・勤務終了が数値で、終了が開始より後にあり、列の開始時刻が勤務内にある場合を塗ります。開始時刻は含め、終了時刻は含めない判定です。
休憩を薄いオレンジにして優先する
同じ範囲を選択したまま、もう1つルールを追加します。数式は次のとおりです。
=AND(ISNUMBER($B2),ISNUMBER($C2),ISNUMBER($D2),ISNUMBER($E2),$D2>=$B2,$E2<=$C2,$E2>$D2,G$1>=$D2,G$1<$E2)塗りつぶしは薄いオレンジ、適用先は勤務と同じ=$G$2:$AD$101にします。この式では、休憩の前後関係に加え、休憩が勤務の範囲内に収まることも条件にしています。
「条件付き書式」→「ルールの管理」を開き、休憩ルールを上、勤務ルールを下に並べてください。同じ塗りつぶしが競合すると、上位のルールが優先されます。「条件を満たす場合は停止」を使える画面なら、休憩側にチェックを入れます。(Microsoft サポート)
凡例は「青＝勤務、薄いオレンジ＝休憩、白＝対象外」として、表の近くに記載します。休憩を白にすると勤務時間外と区別できないため、別の色を使う設計です。
列と行の固定を使い分ける
数式の$は、参照する列や行を固定する記号です。今回の参照は、次のように役割を分けています。(Microsoft サポート)
| 参照 | 固定する部分 | この表での動き |
|---|---|---|
$B2 | B列 | 下の行ではB3、B4を参照する |
G$1 | 1行目 | 右の列ではH1、I1を参照する |
勤務データは「その人の行」、時刻軸は「その列の1行目」を見る必要があります。すべてを$B$2や$G$1のように固定すると、全員が同じ勤務を参照したり、横方向の時刻判定が変わらなくなったりします。
適用先、参照の固定、休憩ルールの順序はセットで確認してください。
手順4：日付の境目と終了時刻を検算する
設定後は、架空のAさんの行を次の表と照合します。すべて30分刻みで入力した場合の期待結果です。
| 列の開始日時 | 確認するセル | 表示 |
|---|---|---|
| 9/15 21:30 | J2 | 白 |
| 9/15 22:00 | K2 | 青 |
| 9/16 0:00 | O2 | 青 |
| 9/16 2:00 | S2 | 薄いオレンジ |
| 9/16 2:30 | T2 | 薄いオレンジ |
| 9/16 3:00 | U2 | 青 |
| 9/16 6:30 | AB2 | 青 |
| 9/16 7:00 | AC2 | 白 |
22時から翌7時までは18枠です。そのうち2時から3時の2枠が休憩色になり、青は16枠になります。0時で帯が途切れず、3時で青に戻り、7時では白になることが確認の要点です。
これはガントチャートの表示検算であり、人数計算ではありません。また、法定休憩やその他の法令適合、給与計算を判定するものでもありません。
結果が一致したら、3行目以降に別の人の勤務を入力します。今回の適用先は101行目までなので、それを超えて追加するときはルールの適用先も広げてください。
15分単位に変更するときは範囲も広げる
今回の数式が塗るのは、「列の開始時刻が勤務・休憩の中にあるセル」です。セルの途中から帯を描く仕組みではありません。
例えば22:15開始の勤務を、22:00のセルの途中から正確に表すことはできません。22:15〜22:25の短い勤務なら、30分刻みの列の開始時刻が一つも含まれず、帯が出ない例になります。
実際の勤務を丸めて書き換えるのではなく、入力時刻に合わせて帯の単位を変更しましょう。15分単位でそろった勤務なら、H1の式を次に変更します。
=$G$1+(COLUMN()-COLUMN($G$1))/96同じ20時から翌8時までを描く場合は、変更箇所を次のようにそろえます。
| 設定箇所 | 30分単位 | 15分単位 |
|---|---|---|
| H1の数式の除数 | 48 | 96 |
| H1からのコピー先 | AE1まで | BC1まで |
| 描画範囲 | G2:AD101 | G2:BB101 |
| 終端の翌8:00 | AE1 | BC1 |
15分幅では48枠になります。勤務・休憩の両ルールの適用先を=$G$2:$BB$101へ変更し、延長した時刻軸にも月日付きの表示形式を設定してください。
なお、22:25終了などは15分単位とも一致しません。より細かい分単位の勤務を扱う場合は、開始・終了・休憩の区切りに合う粒度を選ぶ必要があります。
色が出ないときは入力と設定を切り分ける
白いセルを、そのまま「休み」と判断しないでください。 入力が不正なために条件が成立していない場合もあります。
まず元データを確認し、次に条件付き書式を点検します。
| 症状・入力内容 | 確認と修正 |
|---|---|
| B・Cの片方が空欄、または文字列 | 勤務開始・勤務終了を日時で入力し直す |
| 終了が開始以前になっている | 翌日の終了日を入れ忘れていないか確認する |
| D・Eの片方だけ入力されている | 休憩開始・休憩終了を一組で確認する |
| 休憩が逆転している、勤務外にある | 休憩の日付と時刻を修正する |
| 全員の帯が出ない | G1の年月日と勤務データの年月日を照合する |
| 休憩まで青になる | 休憩入力とルールの優先順位を確認する |
| 一部の行・列だけ反映されない | 適用先と$の位置を確認する |
日時が数値として入っているかを簡単に調べるには、空いているF2へ次の式を入力します。
=ISNUMBER(B2)TRUEなら数値、FALSEなら数値ではありません。FALSEの場合は、元のB2を「標準」の表示形式に戻して日付と時刻を入力し直し、再確認してください。C2〜E2も参照先を替えて調べられます。ただし、TRUEでも日付自体の正しさまでは保証されません。(Microsoft サポート)
休憩入力が不正でも、勤務の青色ルールは成立することがあります。青い帯が出たことだけで入力完了とせず、休憩色まで照合しましょう。
別の日の表へ転用するときも注意が必要です。G1を変更するだけではB〜E列の勤務日付は変わらないため、入力欄と時刻軸を一緒に更新します。
シフト管理では帯表示と人数集計・割り当てを分ける
この表の役割は、入力した勤務と休憩の位置を把握することです。必要人数との比較や勤務時間の合計は、別の集計として扱います。時間の合計は勤務時間を合計する方法、同日内の人数集計は時刻別人数を確認する方法を参照してください。同日内の人数式を夜間へ転用する場合も、日付を含めた比較が必要です。
人数を集計した後の組み直しが増える場合は、シフトラも選択肢です。シフト希望・必要人数・連勤上限などからの勤務案作成と、既存Excelの取り込み・確認確定後の既存帳票への出力に対応しています。(シフトラ) 取り込みの解析結果や出力書式を確認し、勤務案の最終確定は担当者が行います。
まずは1人分で、開始時刻・日付の境目・休憩・終了時刻の検算をそろえましょう。その結果を確認してから対象者を増やせば、どの入力がどの帯に対応するかを確かめながら夜間シフト表を完成させられます。