エクセルでの深夜残業手当の計算方法

質問

お知恵をお貸しください。

現在、A列=出社時間、B列=退社時間、Ｃ列=労働時間（A-B)、という表があります。

これから、以下の４つの答えを取り出したいです。
D列＝深夜残業：労働時間が８時間以上　かつ　２２時以降の労働時間
E列＝深夜：２２時以降の労働時間　から　Ｄ列　をマイナス
F列＝通常残業：労働時間が８時間以上　かつ　２２時未満の労働時間
G列＝通常：労働時間から上記D,E,Fを除いたもの

時間はすべて　13:00:00 の形でデータを保有しています。
どのように関数を書けばよいでしょうか。教えてください。

回答No.3

bunjii · ベストアンサー

用語の誤りを訂正させていただきます。
「出社時間」→「出社時刻」
「退社時間」→「退社時刻」
「22時未満」→「22時以前」（22時は時刻なので時間の計算には以前と以降に同じ時刻を用いても整合性が損なわれません）

>Ｃ列=労働時間（A-B)、という表があります。
休憩時間は考慮しないのでしょうか？

>D列＝深夜残業：労働時間が８時間以上　かつ　２２時以降の労働時間
24時（翌日の0時）を超えて勤務する場合の退社時刻はどのような値になりますか？

>E列＝深夜：２２時以降の労働時間　から　Ｄ列　をマイナス
考え方の問題ですが残業と深夜勤務を別に計算された方が良いと思います。
例えば、15:00:00から24:00:00まで勤務したとき、労働時間が9時間となり、通常勤務を8時間とすれば残業は1時間であり、深夜勤務は2時間になります。
手当の計算では通常の残業手当が時給の125%で深夜勤務が時給の25%として上記の勤務では時給を1000円としたとき次のようになります。
通常勤務=8時間*1000円/時間=8000円
残業手当=1時間*1000円/時間*1.25=1250円
深夜手当=2時間*1000円/時間*0.25=500円
あなたの論理では次のようになるのでしょうか？
通常勤務=8時間*1000円/時間=8000円
深夜残業=1時間*1000円/時間*(1.25+0.25)=1500円
深夜手当=1時間*1000円/時間*0.25=250円
同じ結果を得るためにあなたの論理の方が時間の計算が複雑になります。

>どのように関数を書けばよいでしょうか。
フレックスタイムを利用している場合の勤務条件を整理して条件をもう少し詳しく説明する必要があるようです。
深夜勤務は22時から翌日の5時までと記憶しています。
日付が変わる24:00:00以降に退社する場合の退社時刻が1:30:00のようになるのか、25:30:00となるかの条件が必要になります。
また、24時間以上の勤務が存在するか否かも重要になります。
数式の組み立て方には幾つかの方法があります。
IF関数で条件分岐する方法
MAX関数やMIN関数を駆使する方法
代表的な手法はこの2つと考えられます。
実際の数式は考えられる出退勤の時刻を10組程度提示して頂ければ希望に沿うものを提示できると思います。

お礼

読んでいるうちに頭が整理でき、他の方がくれたシート内の関数と、MAXとmin関数の組み合わせで完成できました。
ありがとうございます。

回答No.4

asciiz

＞現在、A列=出社時間、B列=退社時間、Ｃ列=労働時間（A-B)、という表があります。
＞これから、以下の４つの答えを取り出したいです。

その3つの数字をただこねくり回しても、答えは出ません。
まず、きっちり計算ルールを確定させるところから始める必要があります。

簡単に「深夜」と言いますが、0:00を超えた勤務はどうするのですか?
もし、日付できっちり区切るなら、0:00以降の勤務は「翌日の早朝勤務」で計算することとなり、ご質問にある「深夜残業」には含まれないこととなります。
でもその「早朝勤務」は、「深夜残業」のように割増し計算しますか、しませんか?
区切りを0:00としないなら、何時までの勤務を「前日分」としますか? (例えば05:00)

出社・退社の半端な分数はどのようにしますか? 1分単位まで計算しますか? 10分とか15分単位で切り捨てますか? 1時間未満の端数はすべて切り捨てますか?

そのようなことを決めたうえで、まずは出社時刻・退社時刻より、24時間を「早朝勤務時間」「昼間勤務時間」「通常残業時間」「深夜残業時間」などと区切って、それぞれの部分で、何時間何分勤務したのかを、出します。

さらにご質問にあるルールでは、「一日の勤務時間が8時間を超えるときに、深夜残業の部分を『早朝勤務』と『通常残業』と『深夜残業』に分ける」と言う計算式が必要になります。
またそこで、「早朝勤務」には残業と同等の割り増しをするのか、昼間勤務と同じなのか。

そのぐらいの計算列を作って、ようやく最終的な「早朝勤務時間」「昼間勤務時間」「通常残業時間」「深夜残業時間」の列が出来て、そこに時給と深夜割増等の掛け算ができるようになり、給与計算ができます。

このルールは会社ごとに違うので、ルールに合わせた計算式を設定し、ミスの無いようにコピーしなければなりません。
ルールが変われば、整合性が崩れないように慎重に、式を変更しなければなりません。

はっきり言って、Excelで給与計算をするのは、作るのが大変で、メンテも大変で、どこか間違っていたらすべて自分の責任で、給与の過不足で訴えられる危険性まであるというとてもリスクの高いものになります。

それだから、勤務・給与のパッケージソフトと言うものがあるわけです。
勤務ルールに従った設定を最初に入れ込めば、あとは出社・退社時刻の記録だけで、給与計算ができます。

まあ、Excelの練習に作ってみたいというならそれは勉強ですから構いませんが。
実務用にこれから作ろうとしているんだったら、自分で作るよりもパッケージソフトの導入をお勧めします。
まあさすがに、給与配るのが1人とか2人とかですと、給与ソフト買うのも考えてしまいますが…。
(給与ソフトも買い切りではなく、毎年保守費用がかかるものが多いです。その代わり、保守費用を取るものはインストールや給与ルールの設定方法のサポートを受けることができて、毎年ある税制改正に対するアップデートをしていってくれるので、使う側は勤務時間だけ入れれば正確な給与計算をしてくれます。)

お礼

ご丁寧にありがとうございます。
とりあえず他の方のアドバイスに従ってエクセルでやってみて、パッケージソフトを検討してみます。

回答No.2

Nobu-W

http://mt-soft.sakura.ne.jp/web_dl/excel_shift/help_excel/index.htm

このサイトのシ～ト参照するのもいいかも
シ～トＤＬできるし、応用して自分好みにも大変身させてみて

参考URL：

http://mt-soft.sakura.ne.jp/web_dl/excel_shift/help_excel/index.htm

お礼

ありがとうございます。
見てみます！！

回答No.1

aokii

データ記入方式等により色々と難しい問題がありますので、エクセルから出社時間と退社時間が一致するデータをVLOOKUP関数で抽出してみてはいかがでしょう。

お礼

参考になりました。この関数と他の方が教えてくれた時間を切り分けて考える理屈でできました！！
ありがとうございます。