エクセルで勤怠表（出勤簿）を作るときは、年と月を入れると日付と曜日が自動で並ぶようにし、出勤・退勤・休憩から =D6-C6-E6 で勤務時間、=MAX(0,F6-TIME(8,0,0)) で残業を出します。合計は表示形式を [h]:mm にすると、24時間を超えても正しく表示されます。
この記事では、1か月分の勤怠表を作る手順と式を、実際の Excel の画面で説明します。年・月・時給を変えるだけで毎月使える無料テンプレートも配布しています。
勤怠表の項目と式
| 列 | 項目 | 式（6行目） |
|---|---|---|
| A | 日付 | =IF(A5="","",IF(MONTH(A5+1)=$D$2,A5+1,""))（A5 は =DATE($B$2,$D$2,1)） |
| B | 曜日 | =IF(A6="","",TEXT(A6,"aaa")) |
| C・D・E | 出勤・退勤・休憩 | 手で入力（9:00 のように） |
| F | 勤務時間 | =IF(OR(C6="",D6=""),"",D6-C6-E6) |
| G | 残業 | =IF(F6="","",MAX(0,F6-TIME(8,0,0))) |
1. 年と月から日付と曜日を自動で並べる
B2 に年❶、D2 に月❷を入れます。A5 の =DATE($B$2,$D$2,1) がその月の1日になり、下の行は前の日に1を足していきます。月が変わったら空欄にするので、11月は 11/30 で止まりました。曜日は TEXT 関数で出し、土日は条件付き書式で色を付けています❸（曜日に色を付ける方法）。
2. 勤務時間と残業を出す
出勤・退勤・休憩❹を入れると、F 列に勤務時間❺、G 列に8時間を超えた残業❻が出ます。11/3 は 9:00〜19:30・休憩1時間なので、勤務 9:30・残業 1:30 です。出勤か退勤が空の日は、何も表示しません（時間の足し算・引き算）。
3. 合計・出勤日数・給与を出す
F36 の =SUM(F5:F35) は、表示形式を [h]:mm にして 84:45❼、残業の合計は 5:00 です。出勤日数は =COUNT(F5:F35) で 10日❽、給与は =ROUND(F36*24*$G$2,0) で 101,700円❾になりました。時間は「1日＝1」の数なので、24 を掛けて時間の数にしてから時給を掛けます（時間の表示形式）。
4. 月を変えると、翌月の表になる
D2 を 12 にすると❿、日付が 12/1（火）から 12/31 まで並び直しました⓫。前の月に入れた時刻は残るので、新しい月を使うときは C〜E 列の時刻を消します。毎月シートをコピーして、月だけ変えると、1年分の勤怠表になります。
【実機で検証】11月の入力例の結果
| 項目 | 結果 | 確かめ |
|---|---|---|
| 勤務時間の合計 | 84:45 | 8＋9.5＋8＋7.75＋10＋8＋8.5＋8＋9＋8＝84.75 時間 |
| 残業の合計 | 5:00 | 1.5＋2＋0.5＋1＝5 時間 |
| 出勤日数 | 10 | 時刻を入れた日の数 |
| 給与（時給1200円） | 101,700円 | 84.75 × 1200 |
よくある質問
合計が 12:45 のように少なく表示されます。
表示形式が h:mm のままだと、24時間ごとにくり上がって消えます。合計のセルを [h]:mm にします。
夜勤で日付をまたぐときはどうしますか？
退勤が出勤より小さくなるので、=MOD(D6-C6,1)-E6 のように MOD 関数で計算します（日またぎの時間計算）。
15分単位で切り捨てたいです。
残業時間の計算で、15分単位に丸める式を説明しています。
あわせて読みたい：勤怠と時間計算の関連記事
1日の作業を日報にまとめるなら、業務日報（無料テンプレート）もあわせてご覧ください。
コメント