エクセル 残業時間の計算

質問

いつもお世話になります。

エクセルで勤怠表を作っています。

出勤時間・・・D8
退勤時間・・・D9　　に書く事にした時、

残業時間をD13に表示させたいのですが上手くいきません。

関数を教えて頂けると助かります。

定時時間：8:00～17:00
残業時間：17:01～22:00、5:30～8:00
深夜残業時間：22:01～5:30

尚、時間は30分単位で切り下げです。

宜しくお願い致します。

回答No.9

MackyNo1 · ベストアンサー

補足回答です。

深夜時間は以下のような数式にするのが正しいと思います。

すなわち、開始時間が6：30より前の（5：30分までの）深夜時間と、6：30以降（22：00以降）の深夜時間を合計します。

=(D8<"5:30"*1)*(MIN(D9,"5:30"*1)-D8)＋(D8>"5:30"*1)*MAX(D9-"22:00",0)

前半の式はすでに提示した式で、開始時間が5：30より前の場合は、終了時間と5：30の小さい方の値から開始時間を引いた値を返し、後半の式は終了時間から22：00を引いたものがプラスならその値、マイナスなら0を変える数式を加算したものです。

なお前回の回答で「*1」などの処理に対する説明が不足していたかもしれないので補足すると、文字列の時刻でも引き算や掛け算などの演算をすると、「*1」などの処理をしなくてもクセルが気を利かせて時刻（＝シリアル値）と判断するため、数式内で演算しているような部分では省略することができるわけです。

お礼

何度も丁寧なご説明、ありがとうございます。
とても丁寧で上手な説明で、非常にわかりやすいです。
不明と思っていた箇所もすんなり理解する事が出来ました。
大変勉強になりました。

↓のうまくいかない部分の説明ですが、
5:30から出社した場合ではなく、5:00から出社した場合の間違いでした。

なぜか、5:00～5:30の30分間が深夜残業としてみなかったのですが、
私の認識が違ったようで、この部分もクリアになりました。

他の部分も全てクリアになり、
とてもよく理解できました。

そして、勤怠表ですが、完成させることが出来ましたので、
ご報告とお礼を申し上げます！

また何かありました時はよろしくお願い致します。

今回は貴重はお時間を費やしていただいてありがとうございました。

回答No.10

MarcoRossiItaly

＞ご回答いただきました式を試した所、
　深夜残業時間も残業時間として計算されてしまうようでした。

なるほど「深夜残業時間」というのと「残業時間」を、区別して計算したいわけですね。

No.5で、「「対象外の時間」を除く式」はこうですよ、と説明しました。それを応用すれば、質問者さんがご希望の計算もできてしまいますね？

話を簡単にするため、出勤時間は必ず、午前0時以後である（マイナスからのスタートはない）と決めましょう。また、退勤時間は必ず、32時（午前8時）以前であると決めましょう。

つまり、「深夜残業時間」の計算においては、「対象外の時間」を「0～22時および29:30～32:00（5:30～8:00）」とすればいいですね？

=d9-d8-max(,min(d9,"22:00")-max(d8,"0:00"))-max(,min(d9,"32:00")-max(d8,"29:30"))

また、「残業時間」の計算においては、「対象外の時間」を「0～17時および22:00～29:30（22時～5:30）」とすればいいですね？

=d9-d8-max(,min(d9,"17:00")-max(d8,"0:00"))-max(,min(d9,"29:30")-max(d8,"22:00"))

どうですか？公式として使えるので、とっても簡単でしょう？「max(,min-max)」の意味はちょっと分かりづらいかもしれませんけどね。0～32時までであれば、D8、D9セルに何時を入力しても構いません。

なお、時刻の書式設定ですが、セルの書式設定＞表示形式タブ＞ユーザー定義で、（「h:mm」ではなく）「[h]:mm」に設定しておくと、入力ミスが発生しにくいかと思います。

お礼

大変丁寧なご説明、ありがとうございます。

色々なやり方があるんだなぁとしみじみ拝見させて頂きました。
色々な角度から見ることが大事ですね。

今回はすでに作ってしまっていた分もあり、
他の方の数式を利用させてもらいましたが、
こちらも非常にわかりやすく公式とできそうなので、
しっかり理解し、修得し、今後に生かしていきたいと思います。

また、書式設定の件ですが、[h]が付いた方が入力ミスが発生しずらいんですか！
それも勉強になりました。
早速、書式を変更したいと思います。

理由については自力で調べてみたいと思います。

本当に貴重なお時間を割いていただきありがとうございました。
とても勉強になりました。
また何かありましたら、よろしくお願い致します。

回答No.8

MackyNo1

>=FLOOR((D8<"5:30"*1)*("5:30"-MAX(D8,"0:00"*1))+(D9>"22:00"*1)*((MIN(D9,"29:30"*1))-"22:00"),"0:30")
といった具合で作ってみたのですが、
どうも、22時以降の深夜残業の計算はされるのですが、
5時半以前の部分が思った通りに動きません。

開始時間が5：30よりも前の場合の計算方法（0：00が関係する意味が良くわかりません）が違うのではないでしょうか？
普通なら終了時間と5：30の小さい方の値から開始時間を引けばよいと思います。
=(D8<"5:30"*1)*(MIN(D9,"5:30"*1)-D8)

＞5時半から出社した際の30分間が計算されません。
4時から出社した場合は、1:30とカウントされています。

5：30から出社するどうして30分となるのでしょうか(深夜時間は6：30までなら0：00でよいのでは？）。

＞開始としての0時と終わりとしての24時と、
数式で使用する場合は、どのように記入すればよいかなど合わせて教えて頂けると嬉しいです。

（2日にまたがる場合は同じ時刻でも）開始時間は0：00と入力し、終了時間は24：00とすれば問題が無いと思います。

＞また、時間に1をかける場合と、かけない場合の違いも教えて頂けると嬉しいです。

"5：30"などの「数字」は文字列ですので時刻の大小を直接比較できません(文字列はどんな数値よりも大きい）
そこで文字列の時間をシリアル値に変換するために1を掛けているわけです（Excelが文字列でも演算すると時刻と認識してシリアル値に変換する・・・・日付なども同じ対応が可能です）。
ちなみに「5：30」を別の関数で表示するなら、「5.5/24」あるいは「VALUE("5:30")」などとすることもできます。

回答No.7

MackyNo1

>差支えなければ、式自体の説明も頂けると嬉しいです。
(D8<"8:00"*1)*("8:00"-MAX(D8,"5:30"*1))
↑この部分が特に知りたいです。

上記の数式を通常のIF関数を使った数式にすると以下のようになります。

=IF(D8<"8:00"*1,"8:00"-MAX(D8,"5:30"*1),0)

すなわち、(D8<"8:00"*1)の部分はD8セルの値が8:00よりも小さければ「TRUE」を返し、8：00以上なら「FALSE」を返します。この結果を掛け算すると「TRUE」は1「FALSE」は0と同じ演算をしますので以下のような計算をしていることになります。

開始時間が8:00よりも早ければ、8：00から開始時間と5：30の2つの時刻の遅い時刻の方を引き算して、開始時間が8：00以降の場合は0を返す式になっています。

ちなみに日をまたいで勤務する場合に、終了時間を2：30のように8：00よりも小さい値を入力したときの、終了時間から17：00までの残業時間の計算方法で使用している以下の数式の「(D9<"8:00"*1)」の部分も、例えばD9セルが2：30ではTRUEとなるので1（時刻シリアル値では24：00）を加えた数字すなわち26：30として計算できるようにしています（もちろんIF関数を使った数式にすることもできます）。

=(D9+(D9<"8:00"*1)>"17:00"*1)*(MIN(D9+(D9<"8:00"*1),"22:00"*1)-"17:00")

深夜時間を計算する場合も、実際に入力する時刻のパターン（開始時間が2：00ということはなく終了時間が10：00にならないなど）によって数式を簡略化できる可能性がありますが、基本的に基準時間とのMAXあるいはMIN関数で求めた時刻との差を計算することによって数式を作成することができます。

ご自分で作成した数式でもし期待した値が計算できないようであれば、実際に入力する開始時間と終了時間のパターンをすべて例示してください。

補足

本当に本当にありがとうございます。

説明を読み、ただただすごいの一言です。
そして、丁寧なご説明で、すんなりと理解する事が出来ました。
大変勉強になりました。

そして、教えて頂いた事をもとに
自分なりに深夜残業時間の数式を考え、試してみました。

=FLOOR((D8<"5:30"*1)*("5:30"-MAX(D8,"0:00"*1))+(D9>"22:00"*1)*((MIN(D9,"29:30"*1))-"22:00"),"0:30")

といった具合で作ってみたのですが、
どうも、22時以降の深夜残業の計算はされるのですが、
5時半以前の部分が思った通りに動きません。

5時半から出社した際の30分間が計算されません。
4時から出社した場合は、1:30とカウントされています。

勤怠表の記入方法ですが、
会社の休日の捉え方が、
日曜・・・法定休日（法定残業/法定深夜残業）
土祝・・・通常休日（残業/深夜残業）
と分けてとらえている関係上、

24時を超えての作業となる場合は、

上記が絡まない日については終了時間をその日に25:00といった形で記入し、
上記が絡む日の場合は、一度24時でその日を締め、
次の日に0時より出勤という形にしようと考えています。

私の作成した式がうまく動かない原因の予想ですが、
0:00の書き方がまずかったのかなぁと考えています。

開始としての0時と終わりとしての24時と、
数式で使用する場合は、どのように記入すればよいかなど合わせて教えて頂けると嬉しいです。

また、時間に1をかける場合と、かけない場合の違いも教えて頂けると嬉しいです。

何度もすみませんが、宜しくお願い致します。

回答No.6

KURUMITO

回答Ｎｏ１，２，３です。お示しした式である部分が抜けていました。

=IF(D9+IF(D9<D8,"24:00",0)>"17:00"*1,MIN(D9+IF(D9<D8,"24:00",0),"22:00")-"17:00",0)+IF(D9+IF(D9<D8,"24:00",0)>"29:30"*1,MIN(D9+IF(D9<D8,"24:00",0),"32:00")-"29:30",0)

-"17:00"の後に ,0)　を追加訂正しています。前回の式ですと残業が無い時間ではＦＡＬＳＥになってしまいました。ごめんなさい。

お礼

何度もありがとうございました。

また、訂正までご丁寧にありがとうございます。

早速式を試してみましたが、
早残業の計算が、うまく反映しない様でした。
遅残業の計算は思い通りの計算がされました。

大変参考になりました。
また機会がございましたら宜しくお願い致します。

回答No.5

MarcoRossiItaly

求めたい時間の「対象外の時間」（今回の場合は、定時の範囲内の勤務時間）を除く計算は、次のような式を書きます。遅刻／早退と残業が組み合わさっているケースであっても、残業時間を正しく算出するはずです。

=d9-d8-max(,min(d9,"17:00")-max(d8,"8:00"))

※勤務中に日付が変わる場合の退勤時間は、午前1時を25時などと入力してください。そのほうが式が簡単になります。
※上式の「max(,min-max)」という部分で、遅刻／早退が反映された、定時の範囲内の勤務時間が算出されています。

なお、30分単位で切り捨てるためには、割り算とINT関数を次のように組み合わせるか、FLOOR関数というものを使ってもできます。

=int((d9-d8-max(,min(d9,"17:00")-max(d8,"8:00")))/"0:30")*"0:30"

ただし、マクドナルドによる勤務時間の切り捨て事件と同じで、切り捨て処理はそもそも違法だと判断されることが一般的です。ご質問のケースでは本当に問題ないのか、改めて、よくよく確認された上でご利用くださるようお願いします。

補足

ご回答ありがとうございました。

ご回答いただきました式を試した所、
深夜残業時間も残業時間として計算されてしまうようでした。

求めた形とは違いましたが、
INT関数は使ったことがなかったので、
勉強になりました。

ありがとうございました。

回答No.4

MackyNo1

開始時間から８：00までの残業時間は以下の式で表示できます。
=(D8<"8:00"*1)*("8:00"-MAX(D8,"5:30"*1))

同様に終了時間から17：00までの残業時間は以下の式で表示できます。
=(D9>"17:00"*1)*(MIN(D9,"22:00"*1)-"17:00")

この2つを足した時間は
=(D8<"8:00"*1)*("8:00"-MAX(D8,"5:30"*1))+(D9>"17:00"*1)*(MIN(D9,"22:00"*1)-"17:00")

30分単位で切り捨てた最終的な計算式は
=FLOOR((D8<"8:00"*1)*("8:00"-MAX(D8,"5:30"*1))+(D9>"17:00"*1)*(MIN(D9,"22:00"*1)-"17:00"),"0:30")

ちなみに日をまたいで勤務する場合に、終了時間を2：30のように入力するなら、終了時間から17：00までの残業時間は以下の式で表示できます（D9セルに8：00より小さい値を入力した場合の数式）。
=(D9+(D9<"8:00"*1)>"17:00"*1)*(MIN(D9+(D9<"8:00"*1),"22:00"*1)-"17:00")

上記の原則以外の勤務時間がある場合は、そのパターンを具体的に例示してください。

補足

ご回答ありがとうございます。

ご丁寧に過程を踏んだ説明までして頂き、
大変参考になりました。

ご回答にありました、

30分単位で切り捨てた最終的な計算式は
=FLOOR((D8<"8:00"*1)*("8:00"-MAX(D8,"5:30"*1))+(D9>"17:00"*1)*(MIN(D9,"22:00"*1)-"17:00"),"0:30")

こちらの式がまさに求めていたものでした。

差支えなければ、式自体の説明も頂けると嬉しいです。
(D8<"8:00"*1)*("8:00"-MAX(D8,"5:30"*1))
↑この部分が特に知りたいです。

今、深夜残業時間の計算を、
ご回答いただきました式を参考に自力で考えているのですが、
その参考にさせていただきたいので、
もしお時間がございましたらご教授願います。

回答No.3

KURUMITO

回答Ｎｏ２です。Ｄ8セルとＤ9セルに時間が入力されていない場合にはＤ13セルにはＦＡＬＳＥが表示されますね。
それを避けたいのでしたらＤ13セルに入力する式は次のようにします。

=IF(COUNT(D8:D9)<>2,"",IF(D9+IF(D9<D8,"24:00",0)>"17:00"*1,MIN(D9+IF(D9<D8,"24:00",0),"22:00")-"17:00"+IF(D9+IF(D9<D8,"24:00",0)>"29:30"*1,MIN(D9+IF(D9<D8,"24:00",0),"32:00")-"29:30",0)))

こちらでは実際にデータを入力して試験しておりますので式そのものに問題が有ってエラー表示となっているわけではないでしょう。

補足

何度もすみません＞＜

D8・D9ともに時間を入れていますが、
やはりFALSEとなってしまいます。

表示形式はh:mmとしています。

数式自体は動作確認していただいていますので、
私の問題なんだと思いますが・・・。

原因として考えられる部分は自分でもみてはいるのですが、
結果は同じです。

何か原因として考えられることがありましたらご教授ください。

回答No.2

KURUMITO

Ｄ13に単なる残業時間を表示させるためには次の式を入力します。

=IF(D9+IF(D9<D8,"24:00",0)>"17:00"*1,MIN(D9+IF(D9<D8,"24:00",0),"22:00")-"17:00"+IF(D9+IF(D9<D8,"24:00",0)>"29:30"*1,MIN(D9+IF(D9<D8,"24:00",0),"32:00")-"29:30",0))

Ｄ14セルに深夜残業時間を表示させるためには次の式を入力します。

=IF(D9+IF(D9<D8,"24:00",0)>"22:00"*1,MIN(D9+IF(D9<D8,"24:00",0),"29:30")-"22:00",0)

Ｄ15セルに全残業時間を30分単位で切り下げて表示させるとしたら次の式を入力します。

=FLOOR(SUM(D13:D14),"0:30")

このように30分単位で切り下げるためにはＦＬＯＯＲ関数を使います。
Ｄ13セルやＤ14セルでＦＬＯＯＲ関数を使って30分単位で切り下げることもできますね。その場合には例えばＤ13セルには次のような式を入力します。

=FLOOR(IF(D9+IF(D9<D8,"24:00",0)>"17:00"*1,MIN(D9+IF(D9<D8,"24:00",0),"22:00")-"17:00"+IF(D9+IF(D9<D8,"24:00",0)>"29:30"*1,MIN(D9+IF(D9<D8,"24:00",0),"32:00")-"29:30",0)),"0:30")

なお、Ｄ13，Ｄ14，Ｄ15セルの表示形式は時刻から選択してください。

補足

何度もご回答、本当に感謝いたします。

ご回答頂きました数式を試してみましたが、
FALSEとなってしまいます。

私のやり方が悪いのでしょうか・・・？

大変お手間取らせてしまい恐縮ですが、
お時間がありましたら、
引き続き宜しくお願い致します。

回答No.1

KURUMITO

下記のＵＲＬを参考にしてください。

http://okwave.jp/qa/q7478890.html

補足

ご回答ありがとうございます。

URLを拝見しましたが、
結構特殊なパターンの様で、
私の環境とは大きく違うようでした。

現在の私のスキルでは、
参考にできませんでした。。。

また宜しくお願い致します。