エクセルで勤怠管理表を作っていて、こんなトラブルや面倒くささに悩んでいませんか？

• 「7:45 などの分単位が入ると、途端に合計が狂う」
• 「16:30 に 45分 を足すと、おかしな数字（45日後）になる」
• 「遅刻や早退の判定・不足時間の計算を手動でやるのが面倒…」

この記事では、複雑な計算ロジックを自分で一から考える必要はありません。数式をそのまま貼り付けるだけで、分単位の合計から遅刻・早退の自動判定まで完了します。

具体的な入力手順付きで分かりやすく解説しますので、まずは必要な数式をコピペして使ってみてください！

今回使用するシフト勤怠管理表の基本レイアウト

まずは、以下のレイアウト（2行目）を例にして数式を貼り付けていきましょう。（定時：9:00〜18:00、休憩：1:00 と設定）

分単位が入ってもズレない！「実労働時間」のコピペ数式

「7:45 や 8:12 などの分単位が入ると計算が狂う」という悩みは、分単位ならではの計算ルールとエラー対策を押さえれば即解決します。

■ 分単位の合計でよくある課題

• 未入力セルのエラー問題
  出勤・退勤が空欄のセルがあると、単純な引算式では #VALUE! や ##### といったエラーが発生し集計全体が止まる。
  
• 分単位の端数処理（15分・30分単位）の未対応
  「15分単位切り捨て/切り上げ」などの社内ルールがある場合、手動で修正すると集計漏れやズレの原因になる。
  
  

■ 課題の解決方法

• IF 関数と OR 関数で未入力セルを判定
  出勤・退勤のどちらか一方でも空欄なら計算を行わず「空白」を返すよう条件分岐させる。
  
• FLOOR / CEILING 関数で端数処理を自動化
  15分単位の切り捨て（FLOOR）や切り上げ（CEILING）を数式内で自動的に揃える。

コピペ用数式

【基本パターン】未入力エラーを防ぐ数式（E2セルに入力）

=IF(OR(B2="", C2=""), "", C2-B2-D2)

【応用パターン】15分単位の切り捨て・切り上げを行う数式（E2セルに入力）

=IF(OR(B2="", C2=""), "", FLOOR(C2, "0:15") - CEILING(B2, "0:15") - D2)

■ ここがポイント！

• IF(OR(...)) で未入力エラー（#VALUE!等）を防止
  出勤（B2）や退勤（C2）が空欄の日に無理に引き算を行わず「空白」を表示させるため、分単位の入力があっても集計エラーで全体の計算が止まる心配がありません。
  
• FLOOR / CEILING で端数処理を自動化
  「出勤は15分単位切り上げ（CEILING）」「退勤は15分単位切り捨て（FLOOR）」を数式内で直接計算するため、手動修正による集計漏れやズレをゼロにできます。

SUM関数で1ヶ月分の労働時間を合計したのに「0:00」のまま計算されない時の対処法

「SUM関数で1ヶ月分の労働時間を合計したのに、結果が 0:00 のまま増えない…」という場合、最も多い原因は集計対象のセルが時刻（シリアル値）ではなく「文字列（テキスト）」として入力されていることです。

Excelの SUM 関数は、文字列データを数値として認識できず「0」として無視する仕様があります。見た目が 8:00 と表示されていても、データが文字列になっていると集計結果が 0:00 になってしまいます。

■ 文字列になっているかの確認方法

• セル内で「左揃え」になっている
  数値や正しい時刻データは「右揃え」、文字列は「左揃え」で表示されます。
  
• セルの左上に「緑の三角マーク」が出ている
  「テキスト形式の数値」として認識されているサインです。
  
• 数式内で ="8:00" のように文字列出力している
  ダブルクォーテーションで囲むと計算結果が文字列になります。
  

■ 「0:00」のまま計算されない時の解決策（3つの対処法）

• 方法1：「区切り位置」機能で一括変換する（一番簡単でおすすめ）
  1. 文字列になっている時間のセル範囲を選択します。
  2. メニューの [データ] タブ ＞ [区切り位置] をクリック。
  3. ダイアログが出たら、設定を変更せずそのまま [完了] を押します。（※これで一発で正しい時刻シリアル値に変換されます）
     
     
• 方法2：VALUE関数 / TIMEVALUE関数を使う
  別セルに =VALUE(E2) または =TIMEVALUE(E2) と入力することで、文字列を計算可能な時刻データに変換できます。変換後の列を SUM 関数で合計します。
  
  
• 方法3：数式内で *1 や +0 をして数値化する
  実労働時間を出す計算式の末尾に *1 や +0 を足します。算術計算を強制挟むことで、文字列から時刻シリアル値へ自動変換されます。
  • 例: =IF(OR(B2="",C2=""), "", (C2-B2-D2)*1)

「時刻（16:30）」に「分（45分）」を足し算するとおかしくなる原因と解決策

「16:30 に 45（分）を足したら、変な日付が表示されたり値がおかしくなった…」というトラブルも非常に多く発生します。

原因：Excelの「シリアル値」の仕様

Excel内部では、時刻データを「1日（24時間）＝ 1」とするシリアル値という数値で管理しています。

• 16:30 の内部データ: 0.6875（1日＝24時間のうちの16.5時間分）
• 「45」という数値: Excel内部ではそのまま 「45日」 と解釈されます

そのため = 16:30 + 45 と計算すると「16時30分に45日分を足す」ことになり、45日後の日付時刻や表示エラーになってしまうのです。

分単位の合計計算の解決策（3つの方法）

「45（分）」という単なる整数を、Excelが認識できる時間データ（シリアル値）に変換してから足し算する必要があります。

• 方法1：1日（1,440分）で割って加算する（一番おすすめ）
  1日は 24時間 × 60分 ＝ 1,440分 なので、整数の分を 1440 で割ることでシリアル値に変換できます。
  
  • セルの参照の場合（A2に 16:30、B2に 45）

=A2 + (B2 / 1440)

• 方法2：TIME関数を使う
  TIME(時, 分, 秒) 関数を使って整数を時間の値に変換します。
  
  • セルの参照の場合（A2に 16:30、B2に 45）

=A2 + TIME(0, B2, 0)

• 方法3：加算する時間を「0:45」の形式で指定・入力する
  足したい時間を整数ではなく、最初から 0:45 という時刻形式で入力しておくか、文字列で指定します。
  
  • 直接指定する場合

=A2 + "0:45"

注意：計算結果の表示形式を確認しよう

計算結果のセルが「標準」になっていると、0.71875 のような小数（シリアル値）が表示されることがあります。Ctrl + 1 でセルの書式設定を開き、表示形式を h:mm（または24時間を超える場合は [h]:mm）に変更してください。

手計算ゼロ！「遅刻・早退」の自動判定・時間計算のコピペ数式

遅刻・早退に関しては、「何分（何時間）遅れたか」を算出する数式と、「遅刻」という文字を自動表示させる数式の2通りがあります。用途に合わせて該当するセルに入力してください。

① 遅刻時間を計算する数式【入力セル：F2】

=IF(B2="","", MAX(0, B2 - TIME(9,0,0)))

• 入力位置: F2 セル（遅刻時間の列）
• 参照セル: B2（出勤時刻）
• 解説: 9:00を過ぎて出勤した場合、その差分（遅刻時間）を算出します。9:00以前（定時前）の場合は MAX(0, ...) により 0:00 と表示されます。

② 早退時間を計算する数式【入力セル：G2】

=IF(C2="","", MAX(0, TIME(18,0,0) - C2))

• 入力位置: G2 セル（早退時間の列）
• 参照セル: C2（退勤時刻）
• 解説: 18:00より早く退勤した場合、その不足分（早退時間）を算出します。18:00以降は 0:00 になります。

③ 定時（例：9:00）を過ぎたら「遅刻」と自動判定する数式【入力セル：H2】

=IF(B2>TIME(9,0,0), "遅刻", "正常")

• 入力位置: H2 セル（判定・備考などの列）
• 参照セル: B2（出勤時刻）
• 解説: 出勤時刻（B2）が 9:00 を超えている場合に「遅刻」、それ以外の場合に「正常」と表示します。TIME(9,0,0) 関数を使うことで、文字形式のトラブルによる判定エラーを防ぎます。

④ 数分間（例：5分間）の猶予ルールを組み込む数式【入力セル：H2】

=IF(B2>TIME(9,5,0), "遅刻", "正常")

• 入力位置: H2 セル（判定・備考などの列）
• 参照セル: B2（出勤時刻）
• 解説: 「9:05まではセーフ、9:06以降を遅刻扱いにする」というルールの場合、TIME 関数の分を 5 に設定します。

⑤ 【推奨】未入力（空白）セルの誤判定を防ぐ安全な数式【入力セル：H2】

=IF(B2="", "", IF(B2>TIME(9,0,0), "遅刻", "正常"))

• 入力位置: H2 セル（判定・備考などの列）
• 参照セル: B2（出勤時刻）
• 解説: 休日や欠勤で出勤時刻（B2）が空欄の場合、事前チェックを挟んで「空白」を表示させることで誤判定を回避します。（※「未入力」と文字を出したい場合は "" を "未入力" に変更）
  

⑥ 定時より早い退勤を「早退」と文字判定する（空白対策付き）【入力セル：I2】

=IF(C2="", "", IF(C2<TIME(18,0,0), "早退", "正常"))

• 入力位置: I2 セル（判定・備考などの列）
• 参照セル: C2（退勤時刻）
• 解説: 退勤（C2）が定時（例: 18:00）より前であれば「早退」と表示します。18:00 と直接書かず TIME(18,0,0) を使うことでシリアル値として安全に比較できます。休日や未入力時の誤判定を防ぐため空白チェック付きです。

⑦早退した不足時間を「0:30（時刻形式）」で出したい場合【入力セル：G2】

=IF(AND(C2<>"", C2<TIME(18,0,0)), TIME(18,0,0)-C2, 0)

• 入力位置: G2 セル（判定・備考などの列）
• 参照セル: C2（退勤時刻）
• 解説: 定時（18:00）から退勤時刻（C2）を引いて「何時間何分足りないか」を求めます。セルの表示形式（Ctrl + 1）は h:mm に設定します。

⑧早退した不足時間を「30（数値/分単位）」で出したい場合【入力セル：G2】

=IF(AND(C2<>"", C2<TIME(18,0,0)), (TIME(18,0,0)-C2)*1440, 0)

• 入力位置: G2 セル（判定・備考などの列）
• 参照セル: C2（退勤時刻）
• 解説:

なぜ 1440 を掛けるのか？（計算の仕組み）

エクセル内部では「1日（24時間）＝ 1」という基準で時間を管理しています。そのため、18:00 - 17:30 の計算結果（30分間）は内部で 0.02083...（1日の 1/48 という意味）という小数で扱われています。

1日は 24時間 × 60分 ＝ 1,440分 なので、この小数に 1440 を掛け算することで、私たちが普段使う 「30（分）」という十進数の数字に変換できます。

具体的な表示と設定方法（ビフォーアフター）

• 表示形式の設定（重要）
  数式を入れた直後はエクセルの自動判定で 0:00（時刻形式）と表示されてしまうことがあります。キーボードの Ctrl + 1 を押してセルの書式設定を開き、[標準] または [数値] に変更してください。
  
• 計算結果の表示比較
  • そのまま（TIME(18,0,0)-C2）　⇒ 0:30（※時刻データのためSUM合計や掛け算がしにくい）
  • * 1440 を適用後　⇒ 30　（※「30分」という純粋な数字になるため、給与計算や分数の合計集計がスムーズになる）

• 不足時間に 1日分（1,440分）を掛け算することで「30」などの数値（分）に変換します。集計や給与計算に便利です。セルの表示形式は [標準] または [数値] に設定してください。

注意点：「退勤 – 定時」ではなく「定時 – 退勤」で引き算する

エクセルは標準設定でマイナスの時間を表示できないため、引き算の順序を間違えてマイナスになると ##### とエラー表示されます。不足時間を出す際は必ず 定時時刻 - 退勤時刻 の順で引き算を行います。

「欠勤・遅刻・早退・正常」を1つのセル（1つの数式）でまとめて自動判定する方法

勤怠管理表で最も要望が多いのが、「欠勤」「遅刻」「早退」「正常」という4通りの状態を、1つのセル（1つの数式）で一括判定したいというケースです。

まとめ判定を行う際は、どのような優先順位（順番）で条件判定させるかが最大のポイントとなります。

判定させる条件の優先順位（左から順番に評価されます）

エクセル（IF/IFS関数）は指定した条件を左から順番にチェックしていくため、条件を並べる順番が判定結果を左右します。実務で誤判定を防ぐために、以下の優先順位に従って数式を組み立てていきましょう。

1. 出勤時刻が空欄 ➔ 「欠勤」（※未入力チェックを最優先）
2. 出勤時刻が定時（9:00）を超えている ➔ 「遅刻」
3. 退勤時刻が定時（18:00）より前である ➔ 「早退」
4. 上記のいずれにも該当しない ➔ 「正常」

コピペ用数式

• ① IF関数を重ねる（ネスト）場合【入力セル：H2】（全エクセル対応）

=IF(B2="","欠勤", IF(B2>TIME(9,0,0),"遅刻", IF(C2<TIME(18,0,0),"早退","正常")))

• ② IFS関数を使う場合【入力セル：H2】（Excel 2016以降・Microsoft 365向け）

=IFS(B2="","欠勤", B2>TIME(9,0,0),"遅刻", C2<TIME(18,0,0),"早退", TRUE,"正常")

■ 数式を作成する際の注意点・コツ

• 空欄（欠勤）チェックを先頭にする
  出勤時刻が空欄のまま時間比較を行うと、誤判定やエラーの原因になります。必ず最初に B2="" の判定を挟んでください。

• TIME 関数で時刻を指定する
  "9:00" や "18:00" と文字列で直接比較するのではなく、TIME(9,0,0) や TIME(18,0,0) を使うことで、Excel内部の時間データ（シリアル値）と確実に比較できます。
  
• 「遅刻」と「早退」が同時に起きた場合
  IF関数・IFS関数は左側の条件から順に評価されるため、同じ日に遅刻と早退の両方が発生した場合は「遅刻」が優先表示されます。

エクセルでの勤怠管理・集計に限界を感じたら「シフト作成システム」の検討タイミング！

数式や関数を工夫すればエクセルでも勤怠や遅刻・早退の集計は可能ですが、従業員数が増えたり働き方が多様化したりすると、エクセル運用そのものに不具合や限界が生じやすくなります。

以下のような課題や「現場のサイン」が出始めたら、エクセル管理から専用のシフト・勤怠管理システムへの移行を検討すべきタイミングです。

1. 従業員数が増えて「入力ミス・修正漏れ」の確認作業が限界を迎えたとき

スタッフの人数が増えると、タイムカードからの転記ミスや打刻漏れの確認、手動での数式修正に膨大な時間がかかります。「集計ミスによる給与の誤払い」を防ぐための二重・三重のチェック作業にストレスや限界を感じ始めたら、打刻から集計まで一貫して自動化できるシステムへの移行サインです。

2. 「まるめ処理（端数処理）」や「複雑なシフトパターン」の管理が手動では追いつかなくなったとき

「出勤は15分単位切り上げ」「退勤は15分単位切り捨て」といった端数処理や、早朝・深夜・残業手当、さらには日によって勤務時間が異なるシフト制の計算は、エクセルの数式が非常に複雑化します。数式の破損リスクや計算誤差を抱え続けるよりも、就業ルールを設定するだけで即時自動計算されるシステムを導入する方が安全です。

3. シフトの提出・作成・確定連絡のやり取りを効率化したいとき

エクセルでの勤怠管理に悩む企業の多くは、「シフト表の回収や作成」にも多くの時間を割いています。紙やチャットツールでシフト希望を集め、エクセルに転記し、確定版をまた配る……というアナログな往復作業は、スマホアプリ等で完結するシステムを導入することで劇的に削減できます。

勤怠集計・シフト作成の手間を丸ごと自動化するなら「シフティー」がおすすめ

エクセルでの手入力や複雑な数式管理から完全に解放されたい場合は、シフト作成・勤怠管理システム「シフティー」の活用が効果的です。

シフティーなら、分単位の出退勤集計や15分単位の丸め処理はもちろん、遅刻・早退・残業・深夜時間の自動判定からシフト作成まで、Web上で直感的にスムーズに一括管理できます。「エクセル管理の限界」を感じている方は、ぜひ一度無料体験や資料請求をご検討ください。

まとめ：コピペから始めて、シフト作成のストレスを減らそう

エクセルでのシフト作成・勤怠管理は、分単位の計算やエラーの仕組み（シリアル値）を理解すれば、スムーズに自動化できます。

記事のおさらい（まずやるべき4ステップ）

1. 実労働時間の入力: =IF(OR(B2="",C2=""), "", C2-B2-D2) を貼り付ける
2. 総合判定の入力: IF / IFS 関数で「欠勤 ➔ 遅刻 ➔ 早退 ➔ 正常」を自動判定させる
3. エラーの解消: SUM合計が 0:00 になる時は「区切り位置」機能で時刻データに一括変換する
4. 24時間超え表示: セルの書式設定（Ctrl + 1）を [h]:mm に変更する

まずは今回ご紹介した数式をそのままシートにコピペして、手計算や修正の手間を削減してみてください。

また、「スタッフが増えて転記ミスが減らない」「複雑なシフト制でエクセルの数式が限界」と感じている場合は、無理にエクセルで解決しようとせず、シフト作成・勤怠管理システム「シフティー」の導入をあわせて検討してみるのがおすすめです。お困りの際は、ぜひお気軽にご相談のお問い合わせください。