【EXCEL2007】複雑な時間計算

質問

【EXCEL2007】複雑な時間計算
アルバイトの時給計算表を作成しています。
詰まってしまったところがあるので、ご教示ください。

勤務時間は長時間に渡る者もおり、勤務時間中の時給設定が
「通常勤務(D1)」,「深夜勤務(E1)」,「超深夜勤務(F1)」,「残業(G1)」
と4パタンあります。

店舗の稼働時間としては、17:00～翌8:00までとなり、
A1:出勤時間
B1:退勤時間
C1:休憩時間
に時間を入力(午前1時の場合は25：00)することによって、
各パターンのセルに実際に勤務した時間を自動表示させたいと
思っています。

例）17：00～32：00まで勤務した場合：
D1=5:00
E1=5:00
F1=2:00
G1=3:00

それぞれの時給パターンの条件は以下です。
・通常勤務：17：00～22：00
・深夜勤務：22：00～29：00
・超深夜：そのシフトでの勤務時間が8時間を超える者で8時間を超えた時間から29:00までの時間
・残業：29:00～32:00
・休憩はどのタイミングでとっても、通常勤務時間から引く事とします。

通常勤務(D1)は以下の通りにすることで、表示することができました。

D1=IF(A1>=TIMEVALUE("22:00"),0,IF(D6<=TIMEVALUE("22:00"),(B1-A1),("22:00"-A1-C1)))

が、残りの時給パターンの条件分岐が自分には複雑で詰まってしまいました。
そのものズバリの条件式を教えていただけると幸いですが、
ヒントだけでも、ご教示いただけませんでしょうか。

よろしくお願いいたします。

回答No.3

KURUMITO · ベストアンサー

回答Ｎｏ2です。こちらの計算があっているとのことですので式をお示しします。なお、休憩時間は適当に式を直してください。
Ｄ1セルには次の式を入力します。

=MIN("22:00",$B1)-MAX("17:00",$A1)

Ｅ1セルには次の式を入力します。

=IF(MIN("29:00",$B1)>=MAX("22:00",$A1),MIN("29:00",$B1)-MAX("22:00",$A1),"0:00"*1)

Ｆ1セルには次の式を入力します。

=IF(MIN("29:00",$B1)>=($A1+"8:00"),MIN("29:00",$B1)-($A1+"8:00"),"0:00"*1)

Ｇ1セルには次の式を入力します。

=IF(MIN("32:00",$B1)>=MAX($A1,"29:00"),MIN("32:00",$B1)-MAX($A1,"29:00"),"0:00"*1)

お礼

KURUMITO様

いただいた式を入れましたところ、
思い通りの物で、大変助かりました。

ベスト回答とさせていただきます。

回答No.2

KURUMITO

お示しのケースでは休憩時間を考慮しなければ以下の数字が得られるのですがお示しの答えと違っています。どこが違っているのでしょう。

Ｄ１　　５：００
Ｅ１　　７：００
Ｆ１　　４：００
Ｇ１　　３：００

Ｅ１，Ｆ１のデータが違っています。
なお、休憩時間はどのようになるのでしょう。

補足

すみません。
当方の計算が間違っていました。
KURUMITO様の計算で合っています。
休憩時間は、1時間とるとして、
この場合ですと、
D1＝4:00
です。

よろしくお願いいたします。

回答No.1

grumpy_the_dwarf

とりあえず
http://okwave.jp/qa/q5937258.html
を参考にして下さい。何でもかんでもif関数じゃないんです。

超深夜については、勤務開始から29時と勤務終了のどちらか早い方
までの時間から8時間引いて、ゼロとどちらか多い方って感じかな。

お礼

ご回答ありがとうございました。
MIN・MAXの関数で出来るのですね。
もう少し、この関数を勉強して試してみます。