勤怠管理をエクセルでやる方法｜日またぎ・深夜に対応する式

勤怠をエクセルで付けているのに、日をまたいだ日だけ計算がマイナスになる。

夜のお店で必ず一度はぶつかるところです。

エクセルで勤怠管理をすること自体に問題はなく、詰まるのは日またぎと深夜時間の2か所だけです。

市販のテンプレートは日中勤務を前提に作られているので、終業が始業より小さくなると式が壊れます。

22時から翌5時に当たる時間を切り出す欄も、たいてい付いていません。

この記事では、日またぎを直す式と深夜時間を切り出す式を、そのままコピーできる形で置きます。

Excel以外の選択肢まで含めて比べたい場合は、給与計算を無料でやる3つの方法に夜のお店が共通してつまずく点をまとめています。

読み終えたら、いま使っているファイルの壊れている2か所を自分で直せます。

勤怠管理をエクセルでやるのに必要な列

最低限これだけあれば足ります。

列を増やすほど入力が面倒になって続かないので、まずはこの6列で始めるのがおすすめです。

列	内容	入力
A	日付	手入力
B	出勤時刻	手入力
C	退勤時刻	手入力
D	休憩（分）	手入力
E	労働時間	自動
F	うち深夜時間	自動

B列とC列は「時刻」の書式にしてください（h:mm）。

文字列で入れると計算できません。

退勤が翌日でも、「26:00」のような書き方はしません。

そのまま「2:00」と入れます。

年5日の取得義務があるので、有給を取った日も残せる列を持たせておくと管理が楽になります。付与日数の数え方はシフト制の有給休暇にまとめました。

エクセルの勤怠管理は日をまたぐと壊れる

ここが夜の店で最初にぶつかる壁です。

多くのテンプレートは労働時間を「退勤 − 出勤」で計算しています。

昼の仕事ならこれで合いますが、夜は合いません。

なぜマイナスになるのか

エクセルは時刻を「1日を1とした小数」で持っています。

20:00は0.8333、翌2:00は0.0833です。

そのまま引くとマイナスになり、「#####」と表示されます。

勤務	退勤 − 出勤	結果
9:00 → 18:00	0.75 − 0.375	9時間（正しい）
20:00 → 翌2:00	0.0833 − 0.8333	マイナス18時間（壊れる）

MOD関数で吸収する

解決策はMOD関数です。

MODは割り算の余りを返す関数で、マイナスの値を渡すと自動で1日ぶんを足し戻してくれます。

E2に次の式を入れてください。

=IF(OR(B2="",C2=""),"",MOD(C2-B2,1)*24-N(D2)/60)

やっていることは3つです。

MOD(C2-B2,1)で日またぎを吸収し、*24で「1日＝1」を時間の単位に直し、最後に休憩の分数を時間に直して引いています。

N(D2)を挟んでいるのは、休憩欄が空でもエラーにしないためです。

これで20:00→翌2:00が6.00と出ます。

深夜の時間数をエクセルで切り出す式

労働時間が出せても、それだけでは足りません。

22時から翌5時に当たる時間が何時間なのかを、別に持っておく必要があります。

理由はあとの「必ず入れておく項目」で書きます。

F2に入れる式です。

長いですが、コピーしてそのまま貼れば動きます。

=IF(OR(B2="",C2=""),"",MIN(E2,
 MAX(0,MIN((HOUR(B2)+MINUTE(B2)/60)+MOD(C2-B2,1)*24,5)-MAX((HOUR(B2)+MINUTE(B2)/60),0))
+MAX(0,MIN((HOUR(B2)+MINUTE(B2)/60)+MOD(C2-B2,1)*24,29)-MAX((HOUR(B2)+MINUTE(B2)/60),22))))

実際に入れるときは改行を消して1行にしてください。

読みやすさのために折り返しています。

式の考え方

出勤時刻を起点に、勤務を「数直線の上の区間」として置き換えているだけです。

20:00出勤なら20、翌2:00退勤なら26。

深夜帯を22〜29（＝翌5時）と0〜5の2つの区間として用意して、勤務の区間と重なった長さを足しています。

勤務	数直線では	深夜と重なる長さ
20:00 → 翌2:00	20 〜 26	22〜26 で4時間
18:00 → 23:00	18 〜 23	22〜23 で1時間
0:00 → 8:00	0 〜 8	0〜5 で5時間
17:00 → 22:00	17 〜 22	0時間

0〜5の区間を別に用意しているのは、0時出勤・8時退勤のような朝までの勤務に対応するためです。

休憩が深夜にかかるときは上限を掛ける

式のいちばん外側にMIN(E2, …)が付いているのは安全装置です。

休憩をどの時間帯に取ったかまではデータとして持てないので、休憩は労働時間からだけ引いています。

そのままだと「深夜時間 > 労働時間」になって割増を払いすぎることがあります。

労働時間で頭を押さえておけばその事故は起きません。

深夜帯にはっきり休憩を取っている場合は、その分を手で減らしてください。

式を自分で組むのが面倒なら、組み込み済みのファイルを配布しています。

勤怠の記録だけでよければタイムカードのExcelテンプレート、人の配置を組むならシフト表テンプレート、支給額まで出したいなら給与明細テンプレートです。

1日分だけ確かめたいときは深夜手当の計算ツールが同じ計算をします。

エクセルの勤怠管理で必ず入れておく項目

深夜時間の列をわざわざ作るのは、法律がそれを求めているからです。

労働基準法108条が賃金台帳の作成を義務づけ、施行規則54条がその記載事項を定めています。

……四　労働日数　五　労働時間数　六　法第三十三条若しくは法第三十六条第一項の規定によつて労働時間を延長し、若しくは休日に労働させた場合又は午後十時から午前五時……までの間に労働させた場合には、その延長時間数、休日労働時間数及び深夜労働時間数……

労働基準法施行規則 第54条

「今月は何時間働いた」という合計だけでは、法定の記録として足りません。

深夜労働時間数を分けて持っておく必要があります。

保存期間は当分の間3年

労働基準法109条の本則は「五年間」ですが、附則143条1項が「当分の間、三年間とする」と読み替えを定めています。

いま守るべき期間は3年です。

ファイル名に年月を入れて、月ごとに残してください。

記録は客観的な方法で残す

労働時間の把握は、使用者が自分で確認して記録するか、タイムカードやICカードなど客観的な記録を基礎にするのが原則とされています（厚生労働省のガイドライン）。

やむを得ず本人の自己申告による場合は、実態と離れていないか定期的に確かめることが求められます。

月末にオーナーが記憶で打ち込むのは、この原則から外れます。

その日のうちに入れる運用にしてください。

休憩は開始と終了の時刻で入れる列を持たせます。店内で待たせている時間を休憩にできるかは手待ち時間は休憩にならないにまとめました。

エクセルの勤怠管理に限界が来る3つのタイミング

式さえ入れておけばエクセルで回ります。

ただし回るのはスタッフが数人までの間です。

次のどれかが起き始めたら、続けるほうがコストになります。

1. ファイルが人数分に増えたとき

スタッフごとにファイルを分けると、月末に全部開いて集計することになります。

5人なら5ファイル、時給を変えたときは5箇所を直します。

直し忘れた1箇所が、そのまま支払いのズレになります。

2. 紙の伝票から転記しているとき

店に紙のノートがあって、あとからオーナーがエクセルに打ち直している状態です。

転記そのものに時間がかかるうえ、打ち間違いが混ざります。

しかも打ち直した数字は客観的な記録とは言いにくくなります。

3. 「渡した／もらっていない」が起きたとき

日払いをしている店で必ず起きます。

エクセルは誰がいつ入力したかを残さないので、食い違ったときに確かめる手段がありません。

ここは金額の問題ではなく、信頼の問題になります。

3つとも根っこは同じで、入力がオーナー1人に集中していることです。

スタッフ本人が入れる形にすれば、転記も消えて記録も残ります。

従業員を雇うときの手続き全体は個人事業主が従業員を雇うときに、深夜割増の考え方は深夜手当とは？割増率25%と計算方法にまとめています。

まとめ｜勤怠管理をエクセルでやるときの型

1. 列は6つで足ります。日付・出勤・退勤・休憩・労働時間・うち深夜時間
2. 労働時間はMOD関数で日またぎを吸収する。「退勤 − 出勤」だとマイナスになります
3. 深夜時間は22〜29と0〜5の2区間との重なりで出す。最後に労働時間で上限を掛けます
4. 深夜労働時間数は記録の義務があります（施行規則54条6号）。合計だけでは足りません
5. 保存は当分の間3年（109条・附則143条1項）。月ごとにファイルを分けてください
6. その日のうちに入れる。月末に記憶で打ち込むのは客観的な記録になりません

掲載した式は、この記事のために実際に計算させて検証しています。

ただし賃金の支払いそのものは個別の事情で変わります。

社会保険労務士や所轄の労働基準監督署に確認してください。

エクセルの限界を感じたときの選択肢はシメルンについてに書いています。

この記事で参照した一次情報

• 労働基準法（37条4項・108条・109条・附則143条）
• 労働基準法施行規則（54条）
• 山口労働局「労働時間の『切り捨て』はダメ！」（労働時間の適正な把握・端数処理／PDF）