Excelの時間計算で経過時間から特定の時間を引く方法

設備の稼働日報を作成しているのですが、稼働時間から休憩時間を引いた時間を算出したいです。
工場の休憩時間はタイムテーブルで定められているので、それに該当する時間を稼働時間から引きたいのです。
図の例の赤枠内で言うと、8:30～11:50の稼働時間は3時間20分ですが、そのうち10:00~10:10は休憩時間なので10分を引いて3時間10分とF2に表示させたいのです。
休憩時間は全部で6種類あるので複数の条件を指定しなければならないと思うのですが、その方法が分かりません。
表の列は増えても構わないので、何か良い方法はありますでしょうか？

質問者からの補足コメント

• 画像が小さくて見づらいので貼り直しました。

  
    補足日時：2022/03/22 12:19

No.1 ベストアンサー 回答者： fujillin 回答日時： 2022/03/22 18:58

こんばんは

後で、記すように0:00時をまたぐ計算をすると複雑になるので、連続している時間帯での計算方法のみを記しておきます。

まずは考え方を・・
基本的には、開始～終了の時間帯と一つの休憩時間帯とが重複する時間を求められれば、それを合計すればよいことはお分かりと思います。
時間帯Aと時間帯Bの関係は
　・一方が他方に含まれる
　・一部がラップする
　・重なる時間はない
のいずれかです。
これを調べるのに、開始時刻の遅い方、終了時刻の早い方を計算して、
　開始時刻の遅い方 < 終了時刻の早い方
であれば、重なる時間があると判断できます。

開始時刻の遅い方は、MAX(開始時刻A,開始時刻B）で求められます。--①
終了時刻の早い方は、MIN(終了時刻A,終了時刻B) で求められます。--②
これらから、重複する時間を計算すると、
　(①<②)*(②-①)
で求めることが可能です。（重複が無い場合は0になるような式にしてあります）
これを、ご提示の①～⑤の時間帯に対して計算した和を求めれば良いことになります。
（以下に示す実際の式では、MAX、MIN関数を使うとうまく計算できないので、IF文による式にしてあります）


どこかに休憩時間の表があるものと仮定し、添付図ではB10:C15の範囲をその表としてあります。
添付図ではE2セルに
=SUM((IF(C2<C$10:C$15,C2,C$10:C$15)>IF(B2>B$10:B$15,B2,B$10:B$15))*(IF(C2<C$10:C$15,C2,C$10:C$15)-IF(B2>B$10:B$15,B2,B$10:B$15)))
の関数式を入力し、Ctrl+Shift+Enterで確定しています。
（配列数式なので、↑の確定操作は必須です。）
その上で、下方にフィルコピーしてあります。

※ 上記の式は方法の説明なので、最初にお断りしたように連続した時間帯を対象とした計算になっています。
0:00をまたぐような時間帯がある場合は、25:40のような時刻表記を用いて連続時間帯となるようにするか、あるいは、作業列などを用いて0:00前と後の二つの時間帯に分けて計算してから、合算するような必要があるかも知れません。
（一つの計算式にすることも可能ですが、長くなるだけなので省略しました）
※ A～C列に入力が無い場合に空白にするなどの処理も省いてあります。

この回答へのお礼

ご回答ありがとうございます。
ご教示頂いた数式で時間計算を行うことが出来ました！
日付をまたぐ場合も25:40のような表記にすることで問題無く計算出来ました。他の資料にも応用して時間短縮が図れそうです。
感謝いたします。ありがとうございました！

お礼日時：2022/03/23 15:14