【エクセル】勤務表で残業代を自動計算!その深夜割増し本当に合ってる?

エクセル勤務表・シフト表
管理されてる職場もあると思います!

かじむー

店長さんやアルバイトさんで
勤務表作りで困ってませんか?

特に1日の給与計算をする場合
ちょっと複雑な

残業代!!!

単純に「時給」×「1.25倍」の割増し
…、とはいかないですよね!

深夜帯を考慮すると

え?どこが1.25倍?1.5倍?

かじむー

この記事で数式を組み立てる
ロジックを整理していこう!

残業の計算に必要な「3つの割増」

時間外労働(1.25倍)

■条件
①時間帯  05:00~22:00
②労働時間 8時間を超えた分
③割増率  1.25

みんながよく口にする
普通の残業ですな!

深夜労働(1.25倍)

■条件
①時間帯  22:00~05:00
②労働時間 働いた分の時間
③割増率  1.25

この深夜帯(①の時間帯)に働いた分は
必ず時給が1.25倍になります!

深夜残業(1.5倍)

■条件
①時間帯  22:00~05:00
②労働時間 8時間を超えた分
③割増率  1.5

これは
「時間外労働」かつ「深夜労働」
ということ!

かじむー

つまり残業を計算していくには
これら3つに分けて出す
分かりやすいのだ!

参考:【深夜残業とは】深夜残業の基礎知識と正しい残業代を計算する方法

残業を計算していくロジック

まず[日中]と[深夜]の労働時間を出す

エクセルはこの様に準備しよう!

日中と深夜の時間帯は

日中 05:00~22:00
深夜 22:00~05:00

①実労働

ここの数式は単純に[退勤]-[出勤]-[休憩]だが
[出勤]してない時は何も出したくないので

となりますね!

②日中労働

05:00~22:00で働いた時間ですね!
数式はこうです!

数式の補足①
MAX関数はカッコ内で一番大きい数値を出力
MIN関数はカッコ内で一番小さい数値を出力
数式の補足
MIN(TIME(22,0,0),[@退勤])→最大22時以内の退勤時間を出力
MAX(TIME(5,0,0),[@出勤]))→最小05時以上の出勤時間を出力

③深夜労働

ここは[実労働]-[日中労働]で出ますね!

これで[日中労働]と[深夜労働]の時間が出せましたね!

かじむー

次は[日中]と[深夜]で分けて
給与を計算していこう!

[日中]の給与を計算する

かじむー

ここからは計算するための
ロジックを解説!

ここでは
割増なし[通常代]
時間外労働に当たる[通常残業代]を計算します!

パターン分けは2つです!

①[日中労働] > 8時間の場合

・[通常代] = 8 × 時給
・[通常残業代] = ( [日中労働] – 8 ) × 時給 × 1.25

②[日中労働] ≦ 8時間の場合

・[通常代] = [日中労働] × 時給
・[通常残業代] = 0

[深夜]の給与を計算する

ここでは
深夜労働に当たる[深夜手当]
深夜残業に当たる[深夜残業代]を計算します!

パターン分けは3つです!

①([日中労働]+[深夜労働]) ≦ 8時間の場合

・[深夜手当] = [深夜労働] × 時給 × 1.25
・[通常残業代] = 0

②[日中労働] ≧ 8時間 かつ ①を満たさない場合

・[深夜手当] = 0
・[通常残業代] = [深夜労働] × 時給 × 1.5

③[日中労働] < 8時間 かつ ①を満たさない場合

・[深夜手当] = ( 8 – [日中労働] ) × 時給 × 1.25
・[通常残業代] = { [深夜労働] – ( 8 – [日中労働] ) } × 時給 × 1.5

かじむー

ここはなぜ数式で出さないのか‥?

数式はやめてマクロで作成!

ここの計算を数式で出すには

複雑すぎる!!!

なんとか数式で出せたとしても
後から見ると何やってるか分かりづらく
管理が大変だと思います。

ここをマクロで行うとこんな感じです!


お仕事に数式やマクロなどの
ツール導入をお考えの方は
お気軽にお問い合わせください♪

かじむー

もっとお仕事楽しましょうや!

「効率化」記事