至急助けて下さい！！ エクセル給与計算

至急ご教授下さい！！

給与計算の表を作成しています。
通常勤務時間を求める関数をご教授下さい。

通常勤務時間 7：30-17：30（10時間）
7：30-17：30の場合は引けば10時間とわかるのですが
9：00-18：00（８：30）
3：00-26：30（10：00）
12：00-16：00（4：00）
このように勤務時間がばらばらな場合、時間を入力すると7：30-17：30の間は何時間勤務しているか求めたいです。

御分かりになる方、宜しくくお願い致します。

No.6 回答者： goomania 回答日時： 2021/10/09 17:09

No.5です。

大変失礼しました。前回回答の中にタイプミスがありました。
MIN(VALUE(B6,$B$1)
という数式が登場しますが、括弧の左右が一致していません。
MIN(VALUE(B6),$B$1)
が正しい数式です。お詫びして訂正いたします。

No.5 回答者： goomania 回答日時： 2021/10/09 17:00

No.1さん、No.4さんの数式は参照セルを使用するかしないかの違いはありますが、やっていることは同じです。

大変失礼とは存じますが、お二人の数式では若干問題があります。添付画像①をご覧ください。
1行目から4行目まではご質問者が例示した時間を入力してあります。
1行目から4行目までは何の問題もなく、求めたい時間が表示されています。
5行目をご覧ください。A列18:00、B列18:30という入力に対し、C列「####」が表示されています。本来「0:00」が表示されるべきです。
これは「18:30」と「17:30」の小さいほうを計算に使用して、17:30-18:00を計算することになるため、結果がマイナスになってしまうからです。
また、6行目をご覧ください。A列、B列とも空白(未入力)なのですが、C列は「10:00」と表示されています。
このような状態だと、仮に「複数人の一覧表を作成して集計する」といった場面では未入力の人は「10:00」として計算されることになり、不都合が生じます。
これはMIN(B6,$B$1)が「0:00」ではなく「17:30」を返しているためです。これをMIN(VALUE(B6,$B$1)という数式に差替えれば、「0:00」が返るようになりますが、計算自体は０:00-7:30となり、5行目と同様「####」が返るだけです。
これを回避するため、業務開始時刻、業務終了時刻、所定業務開始時刻、所定業務終了時刻とを比較して、
(1)業務開始時刻が所定業務終了時刻より前であること
(2)業務終了時刻が所定業務開始時刻より後であること
(3)業務開始時刻と業務終了時刻の逆転がないこと
の全てをチェックすることが必要だと思われます。
そこで添付画像➁をご覧ください。C1に記述する数式を

=(MIN(B1,$B$1)-MAX(A1,$A$1))*(A1<$B$1)*(B1>$A$1)*(B1>A1)

と修正することでこの問題を回避できると思います。
なお、「0:00」ではなく、空白を表示したい場合は

=IFERROR((MIN(B1,$B$1)-MAX(A1,$A$1))/(A1<$B$1)/(B1>$A$1)/(B1>A1),"")

でどうでしょうか。

No.4 回答者： mike_g 回答日時： 2021/10/09 07:16

添付図参照(Excel 2019)

￣￣￣￣￣￣￣￣￣￣￣↓↓↓↓↓↓↓↓↓↓↓↓↓

No.3 回答者： トモクンアヤチャン 回答日時： 2021/10/08 23:14

①個々のセルはどのようになっているのでしょうか。

間の「-」は開始時間・終了時間のいずれでもないセルに入ってますか。
例えばA列が開始時間、B列が”-”、C列が終了時間とか。
②昼休みのような休憩時間はないのでしょうか。

No.2 回答者： 中山あ 回答日時： 2021/10/08 19:27

このページが参考になりそうです

https://atsumitm.iobb.net/it/it-046.php#3-3-3

No.1 回答者： fujillin 回答日時： 2021/10/08 19:21

こんにちは

データの値の取り得る可能性がはっきりとはしませんけれど、普通の範囲内と考えても良ければ・・・

A列、B列に時刻データがあるものとして
　=MIN(B1,"17:30")-MAX(A1,"7:30")
とかではいかがでしょうか？