Excelで2パターンの時給が変わる時間計算をしたいのですが、どのようにすればいいか解らない為質問いたします。

Question

Excelで2パターンの時給が変わる時間計算をしたいのですが、どのようにすればいいか解らない為質問いたします。例：7/25 8:30～15:30：A業務 (通常勤務時間：6H、残業:0H)
7/25 15:30～21:30：B業務 (通常勤務時間：2H、残業:4H)
7/26 8:30～17:30：A業務 (通常勤務時間：8H、残業:0H)
7/26 17:30～23:30：B業務 (通常勤務時間：0H、残業:6H、深夜1H)
7/27 8:30～19:30：A業務 (通常勤務時間：8H、残業:2H)
7/27 19:30～20:30：B業務 (通常勤務時間：0H、残業:1H)

※1H休憩時間として引いています。
※業務A:時給1000円、業務B:2000円とする。
※通常勤務時間休憩を除く8時間とする。
※業務選択はプルダウン式

Excelには日付け、開始時間、終了時間、休憩時間(1H)を記入したら、通常勤務時間、実労働時間(トータル時間)、残業時間、深夜時間が分かれて出力されるようにしたいです。

お願いいたします。

Answer 1

貴殿の具体例を基に、下の図のようなことでどうでしょうか？
・日付・開始時刻・終了時刻・休憩時間、は手入力するものとします。
・実労働時間(トータル時間)というのは、休憩時間も含めた開始時刻から終了時刻までの時間とします。
・実労働時間は、通常勤務時間の左側の列に移動しています。
・なお、深夜残業は最大 24:00 までとして式を作っています。

①F2セルには、
=IF(COUNT(C2,D2)<2,0,D2-C2)
の式を入れて、下にオートフィル

②G2セルには、
=IF(COUNT(C2,D2)<2,0,MAX(0,MIN(D2,"17:30")-MAX(C2,"8:30"))-E2)
の式を入れて、下にオートフィル

③H2セルには、
=IF(COUNT(C2,D2)<2,0,D2-C2-E2-G2)
の式を入れて、下にオートフィル

④I2セルには、
=IF(COUNT(C2,D2)<2,0,MAX(0,MIN(D2,"24:00")-MAX(C2,"22:30")))
の式を入れて、下にオートフィル

⑤J2セルには、
=G2*24*IF($B2="A",1000,IF($B2="B",2000,0))
の式を入れて、右と下にオートフィル

しています。

なお、残業時間や深夜残業の賃金がそれぞれ 125% 150% などと割り増しがあるなら、上記⑤の式の、
=G2*24*IF(…
の部分を、
⑥K2セルでは、
=H2*24*125%*IF(…
⑦L2セルでは、
=I2*24*150%*IF(…
などと変更して下さい。

また、時間の表示で「0:00」などと表示されるのが目障りな場合は、
「セルの書式設定」の「ユーザー定義」で h:mm;; と設定して下さい。
さらに、金額の部分で「0」を表示させないなら、
上記と同じ書式設定で #,### と設定して下さい。

Answer 2

A１セルからJ１セルまでは次の列見出しの項目が並んでいるとします。
日付
業務種別ではA業務はA,Bっ業務はBと２行目から下方に表示させます。
E２セルから下方には次の式を入力して下方にドラッグコピーします。

=IF(COUNT(C2:D2)<>2,"",IF(AND(C2<"12:00"*1,D2>"13:00"),"1:00",""))

通常勤務時間のF２セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(C2:D2)<>2,"",IF(MOD(ROW(A1),2)=1,MIN("8:00",D2-C2-E2),MAX(0,MIN("8:00"-F1,D2-C1-E1-F1))))

実労働時間のG２セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(C2:D2)<>2,"",SUM(F2,H2:I2))

残業時間のH２次の式を入力して下方にドラッグコピーします。

=IF(COUNT(C2:D2)<>2,"",MAX(0,MIN(D2,"22:30")-C2-E2-F2))

深夜残業のI２セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(C2:D2)<>2,"",MAX(0,D2-MAX(C2,"22:30")-E2-F2))

賃金のJ２セルには次の式を入力して下方にドラッグコピーします。

=IF(B2="","",G2*24*IF(B2="A",1000,2000))

C列からI列までのセルの表示形式は時刻から好みの表示に変えます。