勤怠表をExcelで自作したいけれど、「残業時間の計算式がよくわからない」「深夜をまたぐと合計時間がおかしくなる」とつまずいた経験はありませんか。実はその崩れ、ほとんどが書式設定のちょっとした落とし穴が原因です。
この記事では、出勤・退勤時刻を入力するだけで勤務時間と残業時間が自動で出るExcel勤怠表の作り方を、そのままコピペで使える数式付きで解説します。手順どおりに進めれば、テンプレートを買わなくても5分ほどで自分専用の勤怠表が完成し、今日から入力だけで運用できます。
▲ Excel サンプル
完成イメージ：こんな表ができます
まずはゴールを共有します。出勤・退勤・休憩の3つを入力すると、実労働時間と残業時間が自動で出る表です。
| 日付 | 出勤 | 退勤 | 休憩 | 実労働 | 残業 |
|---|---|---|---|---|---|
| 6/1 | 9:00 | 18:00 | 1:00 | 8:00 | 0:00 |
| 6/2 | 9:00 | 20:30 | 1:00 | 10:30 | 2:30 |
| 6/3 | 22:00 | 6:00 | 1:00 | 7:00 | 0:00 |
A列＝日付、B列＝出勤、C列＝退勤、D列＝休憩、E列＝実労働時間、F列＝残業時間という構成です。E列とF列が自動計算される部分で、手入力するのは出勤・退勤・休憩の3つだけです。
最初にやるべき書式設定
つまずきの大半はここで防げます。まずB列・C列・D列を選択し、「セルの書式設定（Ctrl+1）」から「時刻」を選んで13:30形式にしておきます。
入力する値は必ず9:00や18:30のように半角コロンで区切ること。900のような数字だけの入力は時刻として認識されず、計算が全部狂います。
休憩列も同じ「時刻」書式です。1時間休憩なら1:00と入力してください。ここで1とだけ打つと「1日分」と解釈されてしまうので要注意です。
なお、Excelは内部で「1日＝1」として時刻を小数で管理しています（12:00なら0.5）。この仕組みを知っておくと、後の数式が腹落ちします。
実労働時間の数式は「夜勤対応版」一本でOK
実労働時間の数式には通常版と夜勤対応版がありますが、迷わないよう最初から夜勤対応版を入れておくのが安全です。日勤しかない人でも結果は変わりません。
E2セルに次を入れます。
=IF(C2<B2,C2+1-B2-D2,C2-B2-D2)
退勤時刻が出勤時刻より小さい（＝日付をまたいだ）場合に+1して1日分を補正します。これで22:00出勤・翌6:00退勤のような夜勤もマイナスにならず正しく計算されます。
入力後、E列の書式は「ユーザー定義」で次を指定してください。
[h]:mm
角かっこ付きの[h]にすることで、25時間や30時間といった24時間超の合計も崩れずに表示できます。
##### やマイナスが出たときの対処
計算結果が#####になるのは、列幅が狭いだけのことが多いので列の境界をダブルクリックして広げれば直ります。マイナスや変な数字が出る場合は、(1)書式が[h]:mmになっているか、(2)出退勤を900のような数字で入れていないか、(3)夜勤版の数式を使っているか、の3点を確認してください。
残業時間を自動計算する
所定労働時間を8時間とする場合、F2セルに次を入れます。
=IF(E2>TIME(8,0,0),E2-TIME(8,0,0),0)
TIME(8,0,0)は「8時間00分」のこと。実労働が8時間を超えた分だけを残業として表示し、超えていなければ0を返します。所定が7時間30分の職場ならTIME(7,30,0)に書き換えるだけです。
月の合計を集計する
表の一番下に合計行を作り、月間を集計します。実労働がE2〜E32にある場合は次のとおりです。
=SUM(E2:E32)
残業合計も同様に=SUM(F2:F32)。合計セルの書式も[h]:mmにしておけば、「今月は172:30働いて、うち残業は18:00」とひと目で把握できます。
残業代の概算を出す小ワザ
残業代だけをざっくり知りたいときは、*24で時刻を数値に変換します（1:30→1.5）。
=F2*24*時給*1.25
割増率25%を掛けた残業分だけの概算です。通常労働分の賃金は含まれない点に注意してください。計算結果のセル書式は「標準」か「通貨」に戻しておきましょう。
コピペ用：全数式まとめ
保存しておけば、次に作るときもこれだけで完結します。
- 実労働（E2）：
=IF(C2<B2,C2+1-B2-D2,C2-B2-D2) - 残業（F2）：
=IF(E2>TIME(8,0,0),E2-TIME(8,0,0),0) - 実労働合計：
=SUM(E2:E32) - 残業合計：
=SUM(F2:F32) - 残業代概算：
=F2*24*時給*1.25
まとめ
ポイントは3つです。第一に時刻セルは必ず「時刻」書式にすること、第二に合計・実労働列は[h]:mmにして24時間超に対応すること、第三に残業はIF関数で所定時間との差を出すこと。
一度テンプレートを作れば、毎月の勤怠管理は入力だけで完結します。まずは1週間分の小さな表から試し、自分の職場のルールに合わせて数式を調整してみてください。
関連記事
- Excelの時間計算で####になる原因と3つの対処法【勤怠表で使える】
- Excelで秒数を時:分:秒に変換｜コピペで使えるTEXT関数と表示形式の使い分け
- Excelで時間を分に変換する方法｜×1440・HOUR・MINUTEの使い分け
- 専用ソフト不要 ― Excelだけで「済」「確認」の朱色スタンプを作ってワンクリックで押す方法
- Excelで○×を集計する方法｜COUNTIF・IFで数える＆点数化【コピペ式付き】
あわせてチェック
この記事を読んだ方におすすめ
【中古】Excel関数［最強］時短仕事術 誰も教えてくれない！仕事がラクになるテ… ¥307
◆◆◆全体的に使用感、日焼けがあります。中古ですので多少の使用感がありますが、品質には十分に注意して販売しております。迅速・丁寧な発送を心がけております。【毎日発送】 商品状態 著者名 守屋恵一 出版…
楽天市場で詳細を見る → 【中古】Excel関数［最強］時短仕事術 誰も教えてくれない！仕事がラクになるテ…
