エクセルの関数について質問です。 A…出勤時間 B…退勤時間 C…総労働時間 D…8時間を超えた労働…

Question

エクセルの関数について質問です。

A…出勤時間
B…退勤時間

C…総労働時間

D…8時間を超えた労働時間
（Cが9.5時間だったら1.5）
※土曜日は4時間を超えた労働時間E…18時以降に働いた時間（18時以降時給が200円上がる為）

例えば9:30-18:30で（休憩1時間）19時まで働くと18:30-19:00の30分がDに出ます。

①18:00-18:30は8時間内なので時給が200円アップだけです
②18:30-19:30はさらに時給に1.25をかけます。

この①②の時間を出す事は可能でしょうか。
土曜日があるのでもうどうしたら良いのかまったくわかりません。一応Dの関数は出来ています。（PC手元にないので式がわかりません）
シフトが固定ではないので9:00-18:00などもあります。

お力いただきたいです。
宜しくお願いします。

Answer 1

曜日がどうなのか、の事は考えず、
１）平日の実働８時間超えたら
の式を完成させる
次に
２）土曜日の場合の実働４時間を超えたら
の式を完成させる

で、
曜日が土曜日なら２の式、それ以外は１の式となる様に

=IF(曜日＝土曜日、２の式、１の式）
とすれば良い。

Answer 2

以下のような関数を使って、C、D、Eの値を計算できます。

C：総労働時間（休憩1時間）
=IF(B2-A2-TIME(1,0,0)<0,B2-A2,B2-A2-TIME(1,0,0))

D：8時間を超えた労働時間（土曜日は4時間超え）
=IF(C2<=8,0,IF(WEEKDAY(A2,2)=6,IF(C2<=12,C2-8,4),C2-8))

E：18時以降に働いた時間
=IF(B2-TIME(18,0,0)<0,0,IF(A2<TIME(18,0,0),B2-TIME(18,0,0),B2-A2))

①18:00-18:30は8時間内なので時給が200円アップだけです
②18:30-19:30はさらに時給に1.25をかけます。

これらの時間を計算するためには、その日の労働時間と、18:00からの労働時間を把握する必要があります。以下の関数を使って、これらの値を計算できます。

F：当日の労働時間（休憩1時間）
=IF(B2-A2-TIME(1,0,0)<0,B2-A2,B2-A2-TIME(1,0,0))

G：18:00からの労働時間
=IF(A2>=TIME(18,0,0),IF(B2>=TIME(19,0,0),TIME(1,0,0),B2-TIME(18,0,0)),IF(B2<=TIME(18,0,0),0,B2-TIME(18,0,0)))

18:00からの労働時間があれば、それに応じて、①と②の時間を計算できます。以下の関数を使って、これらの値を計算できます。

H：18:00-18:30での労働時間
=IF(G2>=TIME(0,30,0),TIME(0,30,0),G2)

I：18:30-19:30での労働時間
=IF(G2>TIME(0,30,0),IF(G2<=TIME(1,0,0),G2-TIME(0,30,0),TIME(0,30,0)+TIME(1,0,0)*1.25),0)

以上の関数を使って、C、D、E、F、G、H、Iの値を計算できます。