勤務時間が所定労働時間を超えた場合にセルを赤く塗るには?
Excelで勤務時間が所定の8時間を超えた場合に、対象のセルを自動で赤く塗りつぶす方法について解説します。
条件付き書式を使用することで、所定労働時間を超えている日を一目で確認できます。
また、夜勤などで日付をまたぐ場合の計算方法や、24時間以上の勤務時間を正しく計算する方法についても説明します。
実働時間を計算し、所定時間を超える条件を条件付き書式に設定します。日またぎと24時間以上の勤務は入力方法を分けます。
1. 勤務時間表の準備と計算式の入力
まずは出社時間、退社時間、休憩時間を入力し、それらを基に実際の勤務時間を計算する表を作成します。
ここでは、同じ日のうちに出社・退社する勤務を例に説明します。
例:勤務時間表のデータ構造
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 出社時間 | 退社時間 | 休憩時間 | 勤務時間 | 所定労働時間 |
| 2 | 9:00 | 18:30 | 1:00 | =B2-A2-C2 |
8:00 |
表の数式はそのセルへの入力例です。Excel上では、数式を確定すると計算結果が表示されます。
休憩時間が45分の場合は「0:45」と入力してください。
また、計算結果を勤務時間として表示するため、D列の表示形式は[h]:mmに設定することをおすすめします。
勤務時間の表示形式を設定する手順
- 勤務時間を表示するD列の対象範囲を選択します。
- Ctrl + 1キーを押して「セルの書式設定」を開きます。
- 「表示形式」タブから「ユーザー定義」を選択します。
- 種類に[h]:mmと入力します。
- 「OK」をクリックします。
「h:mm」は通常の時刻表示に使用する形式ですが、[h]:mmは24時間以上の経過時間も表示できる形式です。
例えば、25時間の場合、「h:mm」では「1:00」と表示されることがありますが、「[h]:mm」では「25:00」と表示できます。
ただし、表示形式を変更しても計算式そのものが修正されるわけではありません。正しい勤務時間を計算するためには、開始・終了時刻の入力方法にも注意が必要です。
数式の解説
=B2-A2-C2:退社時間から出社時間と休憩時間を引き、実働時間を算出します。
今回の例では、9:00から18:30までの経過時間は9時間30分です。
そこから休憩時間の1時間を引くため、勤務時間は8時間30分(8:30)となります。
この数式は、A2とB2に時刻だけを入力する場合、同じ日のうちに出社・退社する勤務を想定しています。
2. 条件付き書式の設定手順
勤務時間(D2セル)が、所定労働時間である8時間(E2セル)を超えたら、自動で赤く塗りつぶす設定を行います。
以下では、E2に入力した所定労働時間を、各行に共通の判定基準として使用します。
- 勤務時間が入力されているセル(例:D2)を選択します。複数行を対象とする場合は、D2:D100などの範囲を選択します。
- 「ホーム」タブにある「条件付き書式」をクリックし、「新しいルール」を選択します。
- ルールの種類から「数式を使用して、書式設定するセルを決定」を選択します。
- 「次の数式を満たす場合に値を書式設定」の入力欄に、以下の数式を入力します。
=D2>$E$2- 「書式」ボタンをクリックし、「塗りつぶし」タブから赤色または薄い赤色を選択します。
- 「OK」をクリックし、条件付き書式の設定を完了します。
これにより、勤務時間が所定労働時間の8時間を超えた場合に、そのセルが赤く塗りつぶされます。
今回の例では、D2の勤務時間は8:30、E2の所定労働時間は8:00なので、D2が赤く表示されます。
数式のポイント
$E$2は、E2セルを絶対参照する指定です。
今回の例では、全員・全行に共通する所定労働時間をE2に入力しているため、絶対参照を使用しています。
一方、各行のE列に異なる所定労働時間を入力する場合は、次の数式を使用します。
=D2>E2この場合、D3はE3、D4はE4というように、同じ行の所定労働時間と比較します。
3. 日付をまたぐ勤務時間を計算する場合
夜勤などで退社時間が翌日になる場合、出社・退社の時刻だけを使って単純に引き算すると、正しい勤務時間を計算できないことがあります。
例えば、20:00に出社し、翌日の5:30に退社する場合です。
時刻だけを入力した状態で、次の数式を使用すると、計算結果が負の値になります。
=B2-A2-C2Excelの通常の日付・時刻表示では、負の時間が「#####」のように表示されることもあります。
このような場合、出社から退社までの経過時間が24時間未満であることが確実なら、MOD関数を使用できます。
24時間未満の日またぎ勤務に使用する数式
=MOD(B2-A2,1)-C2数式の解説
- B2-A2:退社時刻から出社時刻を引きます。
- MOD(B2-A2,1):時刻の差を1日(24時間)未満の範囲に収めます。翌日退社によって差が負になる場合も、24時間以内の経過時間として計算できます。
- -C2:経過時間から休憩時間を引き、実働時間を求めます。
計算例
| 項目 | 入力値 |
|---|---|
| 出社時刻(A2) | 20:00 |
| 退社時刻(B2) | 5:30(翌日) |
| 休憩時間(C2) | 1:00 |
| 勤務時間(D2) | =MOD(B2-A2,1)-C2 |
| 計算結果 | 8:30 |
20:00から翌日5:30までの経過時間は9時間30分です。
そこから休憩時間の1時間を引くため、勤務時間は8時間30分となります。
このように、24時間未満の日またぎ勤務であれば、MOD関数を使って計算できます。
4. MOD関数では24時間以上の勤務を正しく計算できない
MOD関数を使った数式は、24時間以上の勤務時間の計算には使用できません。
MOD関数は、指定した数値を除数で割った余りを返す関数です。
Excelでは1日が数値の「1」に相当するため、次の数式は経過時間を24時間未満の範囲に収めます。
=MOD(B2-A2,1)そのため、24時間分、48時間分などの経過時間は計算結果から失われます。
例えば、実際には26時間経過していても、MOD関数による計算では2時間として扱われます。
24時間以上の勤務で問題となる例
次のような勤務を考えます。
- 出社:6月18日 9:00
- 退社:6月19日 11:00
- 経過時間:26時間
- 休憩時間:1時間
- 実働時間:25時間
出社・退社を時刻だけで入力すると、次のようになります。
- A2:9:00
- B2:11:00
- C2:1:00
この状態で次の数式を使用すると、
=MOD(B2-A2,1)-C2計算結果は1:00となります。
実際の勤務時間は25時間なので、24時間分の誤差が発生します。
また、出社と退社が同じ時刻で、実際にはちょうど24時間経過している場合も、時刻だけでは0時間と区別できません。
このような勤務では、出社日と退社日を含めた日時を記録する必要があります。
5. 24時間以上の勤務は日付と時刻を入力して計算する
24時間以上の勤務や、複数日にまたがる勤務を正しく計算するには、出社・退社を時刻だけではなく、日付と時刻を組み合わせた日時データとして入力します。
この方法であれば、Excelが日付の差も含めて計算するため、MOD関数は不要です。
入力例
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 出社日時 | 退社日時 | 休憩時間 | 勤務時間 | 所定労働時間 |
| 2 | 2026/06/18 9:00 | 2026/06/19 11:00 | 1:00 | =B2-A2-C2 |
8:00 |
表の数式はそのセルへの入力例です。Excel上では、数式を確定すると計算結果が表示されます。
数式
=B2-A2-C2この数式では、日付と時刻の両方を含む値を使用するため、退社日時から出社日時を引くことで正しい経過時間を求められます。
計算結果
- 2026年6月18日9:00から2026年6月19日11:00まで:26時間
- 休憩時間:1時間
- 実働時間:25時間(25:00)
勤務時間を表示するD2セルの表示形式を[h]:mmに設定すると、結果は「25:00」と表示されます。
一方、通常の「h:mm」形式では、24時間を超える部分が表示に反映されず、「1:00」のように見えることがあります。
そのため、24時間以上の勤務を扱う場合は、日時データによる計算と、[h]:mmによる表示を組み合わせることが重要です。
この方法は、24時間未満の日またぎ勤務にも利用できます。勤務時間を継続的に管理する場合は、最初から出社・退社を日時で入力する方法が確実です。
6. 勤務時間を管理する際の注意点
- 日付をまたぐ勤務:時刻だけを入力するMOD関数の方法は、出社から退社までの経過時間が24時間未満である場合に限ります。
- 24時間以上の勤務:出社・退社の両方を日付付きの日時データとして入力してください。
- 表示形式:勤務時間の計算結果は、[h]:mm形式で表示することをおすすめします。
- 休憩時間:休憩時間は1:00や0:45など、Excelが時間として認識できる形式で入力してください。休憩時間が経過時間を超えるなど、入力内容に誤りがある場合は正しい実働時間を求められません。
- 所定労働時間:所定労働時間が全行で共通する場合は$E$2を参照します。行ごとに異なる場合はE2のような相対参照を使用してください。
- 労務管理上の扱い:所定労働時間を超えたことと、法定時間外労働に該当することは必ずしも同じではありません。割増賃金などの計算では、法定労働時間や休日、深夜労働なども別途考慮する必要があります。
まとめ
Excelの条件付き書式で次の数式を設定すると、勤務時間が所定労働時間を超えた場合に、対象セルを自動的に赤く塗りつぶせます。
=D2>$E$2勤務時間の計算方法は、勤務の状況に応じて使い分けてください。
| 勤務の状況 | 計算式 |
|---|---|
| 同じ日のうちに出社・退社する場合 | =B2-A2-C2 |
| 24時間未満の日またぎ勤務(時刻のみ入力) | =MOD(B2-A2,1)-C2 |
| 24時間以上の勤務・複数日にまたがる勤務(日時を入力) | =B2-A2-C2 |
MOD関数は24時間未満の経過時間を求める方法であり、24時間以上の勤務時間を正しく計算できるわけではありません。
24時間以上の勤務を扱う場合は、出社日時と退社日時を入力し、その差から休憩時間を引いてください。
勤務時間の表示形式を[h]:mmにしておけば、25時間などの長い勤務時間も正しく表示できます。
所定労働時間の基準をE2セルで管理すれば、基準が変更された場合もE2の値を更新することで、条件付き書式の判定を変更できます。