Excelで勤怠管理表を作る方法｜残業時間も自動計算するテンプレート

私自身、勤怠表を作るたびに『また残業時間の計算で詰まった』と思っていました。完全自動化を目指したシートの組み方です。

この記事でわかること

内容	レベル
勤怠管理表の基本レイアウト	★☆☆（かんたん）
実働時間の自動計算	★★☆（ふつう）
残業時間の自動計算	★★☆（ふつう）
月合計・日数カウント	★★☆（ふつう）
30分単位の丸め処理	★★★（少しむずかしい）

「毎月の勤務時間を手計算するのが大変」「残業時間を正確に記録したい」——そんな悩みはExcelで解決できます。

勤怠管理ソフトを導入するほどでもない小さな事業所、アルバイトの自己管理、在宅勤務の記録など、Excelなら自由にカスタマイズできて便利です。

この記事では、出勤・退勤時刻を入力するだけで実働時間と残業時間を自動計算してくれる勤怠管理表の作り方を、ゼロから解説します。

ステップ1：勤怠管理表のレイアウトを作る

セル	入力する内容	意味
A1	日付	勤務した日
B1	曜日	自動で表示される
C1	出勤時刻	例：9:00
D1	退勤時刻	例：18:30
E1	休憩時間	例：1:00
F1	実働時間	自動計算される
G1	残業時間	自動計算される
H1	備考	休暇理由などメモ

A列の日付は、A2に「2026/4/1」と入力して、下にドラッグすれば1か月分が自動で入ります（オートフィル機能）。オートフィルの使い方はこちらの記事で詳しく解説しています
→ オートフィルの使い方

ステップ2：曜日を自動表示する（TEXT関数）

B列に曜日を自動で表示させます。使うのはTEXT関数です。

B2セルに次の数式を入力してください。

=TEXT(A2,”aaa”)

B2を下にコピーすると、全ての日付に対応する曜日が表示されます。

「aaa」は「月」「火」などの1文字の曜日、「aaaa」なら「月曜日」のように4文字の曜日になります。詳しくはこちらの記事で解説しています → 曜日を自動表示する方法

数式	表示例
=TEXT(A2,”aaa”)	月
=TEXT(A2,”aaaa”)	月曜日
=TEXT(A2,”ddd”)	Mon

ステップ3：実働時間を自動計算する

ここからがメインです。出勤時刻・退勤時刻・休憩時間から、実際に働いた時間を計算します。

F2セルに次の数式を入力してください。

=IF(OR(C2=””,D2=””),””,D2-C2-E2)

この数式の意味を分解して説明します。

部分	意味
IF(OR(C2=””,D2=””),””,…)	出勤か退勤が空欄なら何も表示しない
D2-C2	退勤時刻 − 出勤時刻
-E2	休憩時間を引く

F2に入力したら、下のセルにコピーしてください。

入力例：出勤9:00、退勤18:30、休憩1:00 → 実働時間は 8:30（8時間30分）

ステップ4：時刻の表示形式を設定する

計算はできても、表示がおかしくなることがあります。以下のように表示形式を整えましょう。

手順はこちらです。

1. C列からF列、G列を選択（出勤〜残業時間の列）
2. 右クリック →「セルの書式設定」
3. 「表示形式」タブ →「時刻」を選択
4. 「13:30」の形式を選ぶ
5. 「OK」

これで全て「時:分」の形式で表示されます。

もし「####」と表示されたら、列の幅が足りないだけです。列の境目をダブルクリックすれば自動で広がります。

ステップ5：残業時間を自動計算する

1日8時間を超えた時間を残業として自動計算します。

G2セルに次の数式を入力してください。

=IF(F2=””,””,MAX(F2-“8:00”,0))

この数式の意味はこちらです。

部分	意味
IF(F2=””,””,…)	実働時間が空欄なら何も表示しない
F2-“8:00”	実働時間から8時間を引く
MAX(…,0)	マイナスになったら0にする（8時間未満は残業0）

G2を下にコピーしてください。

入力例：実働8:30 → 残業 0:30（30分）
入力例：実働7:30 → 残業 0:00（残業なし）

ステップ6：月合計を計算する

月末に合計欄を作ります。A33あたりに「合計」と入力して、以下の数式を入れましょう。

セル	数式	意味
F33	=SUM(F2:F32)	実働時間の合計
G33	=SUM(G2:G32)	残業時間の合計

ただし、24時間を超えると表示がおかしくなります（例：40時間なのに16:00と表示される）。これを解決するには、表示形式を特別な形に設定します。

1. F33とG33を選択
2. 右クリック →「セルの書式設定」
3. 「表示形式」タブ →「ユーザー定義」
4. 「種類」の欄に [h]:mm と入力（hを角カッコで囲むのがポイント）
5. 「OK」

これで「180:30」のように24時間を超えた合計も正しく表示されます。

ステップ7：出勤日数・休日日数をカウントする

出勤日数もCOUNTA関数で自動計算できます。

セル	数式	意味
C33	=COUNTA(C2:C32)	出勤時刻が入っている日数（＝出勤日数）
H33	=COUNTIF(H2:H32,”有給”)	H列に「有給」と入力された日数

COUNTIF関数を使えば、備考欄から有給・欠勤・特休などを自動で数えられます。

ステップ8：土日の行に色をつける（条件付き書式）

土曜日は青、日曜日は赤で自動的に色がつくようにしましょう。見やすさが格段にアップします。

手順はこちらです。

1. A2からH32を選択（表全体）
2. 「ホーム」タブ →「条件付き書式」→「新しいルール」
3. 「数式を使用して、書式設定するセルを決定」を選ぶ
4. 以下の数式を入力

=WEEKDAY($A2)=1

5. 「書式」ボタン →「塗りつぶし」で薄い赤色を選ぶ
6. 「OK」で確定

次に土曜日用も追加します。

7. もう一度「条件付き書式」→「新しいルール」
8. 数式：=WEEKDAY($A2)=7 を入力
9. 書式：塗りつぶしで薄い青色
10. 「OK」

数式	意味
=WEEKDAY($A2)=1	日曜日（1番目の曜日）
=WEEKDAY($A2)=7	土曜日（7番目の曜日）

条件付き書式の詳しい使い方はこちらの記事で解説しています → 条件付き書式の使い方

応用：30分単位に丸める方法

会社によっては「30分単位」「15分単位」で勤怠を記録するルールがあります。FLOOR関数とCEILING関数を使えば、自動で丸められます。

やりたいこと	数式の例	結果の例
出勤時刻を30分単位に繰り上げ	=CEILING(C2,”0:30″)	9:10 → 9:30
退勤時刻を30分単位に切り捨て	=FLOOR(D2,”0:30″)	18:25 → 18:00
実働時間を15分単位に切り捨て	=FLOOR(F2,”0:15″)	8:20 → 8:15

CEILING は「切り上げ」、FLOOR は「切り下げ」と覚えましょう。労働基準法では労働者に不利になる切り捨ては原則NGなので、運用時はルールを確認してください。

完成した勤怠管理表の全体像

機能	使う関数・機能
曜日の自動表示	TEXT関数
実働時間の計算	IF関数＋時刻の引き算
残業時間の計算	IF関数＋MAX関数
月合計	SUM関数＋[h]:mm の表示形式
出勤日数カウント	COUNTA関数
土日の色分け	条件付き書式＋WEEKDAY関数
30分単位の丸め	CEILING関数・FLOOR関数

使い方と運用のコツ

毎日の運用はシンプルです。

1. 朝、出勤したらC列に時刻を入力（例：9:00）
2. 夜、退勤したらD列に時刻を入力（例：18:30）
3. 休憩を取ったらE列に時間を入力（例：1:00）
4. 実働時間・残業時間は自動で計算される

月が変わったら、シートをコピーして次の月用に使い回すのが便利です。シートのコピー方法はこちらの記事をご覧ください → シートをコピー・移動する方法

よくあるトラブルと対処法

トラブル	原因	対処法
合計が24時間を超えると変な表示になる	時刻の標準表示は24時間でリセット	表示形式を [h]:mm に変更
####と表示される	列の幅が足りない	列の境目をダブルクリックで自動調整
マイナスの時間になる	深夜勤務で日をまたいでいる	=MOD(D2-C2,1) のようにMOD関数を使う
残業時間が正しく出ない	時刻ではなく数値として扱われている	セルの書式設定で時刻に変更

まとめ

ポイント	内容
基本構成	日付・出勤・退勤・休憩・実働・残業・備考の7項目
実働時間	退勤 − 出勤 − 休憩（IF関数で空欄対応）
残業時間	MAX関数で「実働 − 8時間」を計算（マイナスは0に）
月合計の表示	[h]:mm 形式で24時間超えにも対応
見やすさアップ	条件付き書式で土日を色分け

有料の勤怠管理ソフトを使わなくても、Excelだけで十分な勤怠管理ができます。まずはこの記事のとおりに作ってみて、自分の会社のルールに合わせてカスタマイズしていきましょう！

関連記事

• Excelで曜日を自動表示する方法【日付から自動で】
• Excelで家計簿を作る方法【テンプレートなしでゼロから】
• Excelで在庫管理表を作る方法