エクセルでの時間外労働の時間の算出

質問

（各セルには　ｈｈ：ｍｍ形式で時刻を入力）
例えば　ａ１セルに始業時刻９：００、ｂ１セルに休憩時間１：００、ｃ１セルに終業時刻２３：３０とした場合、　ｄ１に　９：３０～午後１０時（２２：００）までの時間のうち　休憩時間（ｂ１に任意入力、この場合では１：００）を除いた実動時間から８時間（法定労働時間）を引いた通常の時間外労働時間を、ｅ１には　深夜労働となる２２：００を超えて２３：３０（ｃ１セル）までの時間を表示する方法を教えてください。

回答No.1

ASIMOV · ベストアンサー

＞ａ１セルに始業時刻９：００
＞ｄ１に　９：３０～午後１０時（２２：００）までの

この、３０分のずれはそのまま計算して良いのでしょうか？
であれば

D1 =IF(C1>TIME(22,0,0),"22:00"-A1,C1-A1)-B1-"0:30"
E1 =IF(C1>TIME(22,0,0),C1-"22:00",0)

で、どうでしょうか
D1の右端の -"0:30" を消せば、始業時からの計算になります

お礼

３０分のずれの件は質問者の間違い表記でした。
「ｄ１に　９：３０～午後１０時（２２：００）までの」の９：３０は９：００が正しい表記でした。Ｄ１右端の－"0:30"は消しました。

あと教えたいただいた分では　法定時間８時間の控除の計算部分がなかったので消したＤ１右端部分に　－"８：００"を加えました。
これで例示の計算はきちっとできました。ありがとうございます。

ただ、Ｃ１に　１７：３０とか　１８：００より前の時刻を入力すると、「負であるか・・・」の＃＃＃のエラーとなります。　こういう場合には　０：００　表示または　空白（””） にしたいのですが、どんなものでしょうか。
１８：００より前の時刻とは　終業１８：００－始業９：００－休憩１：００－法定労働時間８：００＝＜８：００　で　時間外が発生しない時刻です。
再度の質問で恐縮ですが、お教えいただければ喜びます。

回答No.3

ASIMOV

＞ただ、Ｃ１に　１７：３０とか　１８：００より前の時刻を入力すると、「負であるか・・・」の＃＃＃のエラーとなります。　こういう場合には　０：００　表示または　空白（””） にしたいのですが、どんなものでしょうか。

IF をもう一段足して

D1 =IF(C1>TIME(18,0,0),IF(C1>TIME(22,0,0),"22:00"-A1,C1-A1)-B1-"8:00",0)

こんなんでどうでしょうか

お礼

何度もありがとうございます。
IF(C1>TIME(18,0,0)を加えた場合、始業時刻が９：００である今回の事例では大丈夫ですが、
始業時刻 7:00、休憩　1:00、終業　17:00という例にした場合　時間外は１時間発生しますが、D1は　0:00 になってしまいました。

加えていただいた　IF(C1>TIME(18,0,0)　を IF(C1-A1-B1<=TIME(8,0,0) と修正させてもらい

D1=IF(C1-A1-B1<=TIME(8,0,0),0,IF(C1>TIME(22,0,0),"22:00"-A1,C1-A1)-B1-"8：00")

でうまくいきました。
これで大丈夫だと思います。大変ありがとうございました。
（初めは　TIME(8,0,0)　を　"8:00" としたのですが うまくいかなか　ったのです。両者の違いがすっきりとわからない状態なのでまた勉強　してみます）

回答No.2

don9don9

何だか式が複雑になってしまいましたが、以下でどうでしょうか。

E1の式として
=IF(AND(ISBLANK(A1)=FALSE,ISBLANK(C1)=FALSE),IF(C1<A1,IF(C1>"5:00"*1,"5:00"*1+1,C1+1)-IF(A1<"22:00"*1,"22:00"*1,A1)+IF(A1<"5:00"*1,"5:00"*1-A1,"0:00"*1),IF(A1<"5:00"*1,IF(C1<"5:00"*1,C1,"5:00"*1)-A1,"0:00"*1)+IF(C1>"22:00"*1,C1-"22:00"*1,"0:00"*1)),"0:00"*1)

深夜労働時間は22:00～5:00で設定。

開始時刻＞終了時刻となった場合（0:00をまたいだ場合）
終了時刻＋1日と開始時刻の差を求め、その内の22:00～5:00の間の時間数を算出

上記以外の場合（開始時刻≦終了時刻、0:00をまたがない場合）
開始時刻が5:00以前、終了時刻も5:00以前の場合、開始時刻から終了時刻までの時間数
開始時刻が5:00以前、終了時刻が5:00以降の場合、開始時刻から5:00までの時間数
終了時刻が22:00以降の場合、22:00から終了時刻までの時間数
これを合算したもの

D1の式として（E1の式の結果を使います）
=IF(AND(ISBLANK(A1)=FALSE,ISBLANK(C1)=FALSE),IF(IF(C1<A1,C1+1-A1,C1-A1)-B1-"8:00"*1-E1<=0,"0:00"*1,IF(C1<A1,C1+1-A1,C1-A1)-B1-"8:00"*1-E1),"0:00"*1)

開始時刻＞終了時刻となった場合は終了時刻＋1日と開始時刻の差
それ以外の場合は終了時刻と開始時刻の差を求め、ここから
B1の休憩時間、E1の深夜時間、8時間を引いたもの
（計算結果がマイナスになった場合は0:00）

お礼

お礼遅くなりました。

そのまま式を適用したら、うまく表示できました。

翌日へまたぐ場合、深夜時間帯もよく考えていただきありがとうございます。
使ったことのない関数もあり、式の意味を自分なりに理解してみたいと思います。

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