作業時間をエクセルで計算させたい

作業時間をエクセルで計算させたいのですが、よろしくお願いします。
生産ラインにインしてからアウトまでの作業時間を関数で計算したいです。

条件は下記になります。

1.作業時間 09:00～17:30
2.休憩時間 01:00(12:00～13:00)
3.残業はしない
4.時間は秒単位(もしくは分単位)
5.休日、祝祭日はカレンダーを参照にしたい
※別シート参照など
6.できればマクロではなく関数で計算させたいです。
7.作業を開始してから作業終了までが日をまたいだり、
　週をまたいだりすることがある。
※例としては金曜日(10:00)に作業を開始して、
　月曜日(15:00)に作業終了する場合など、

作業時間の結果はE列にできるようにしたい

どうかご教授のほど、宜しくお願い致します。

No.1 ベストアンサー 回答者： 銀鱗 回答日時： 2016/04/11 22:30

E2セルには

=(C2+D2)-(A2+B2)
これで開始から終了までの経過時間は計算できる。
日を跨いでも問題ない。

しかし休憩時間を考慮する必要があるので、別途休憩時間が経過時間の中に含まれるかを計算する必要がある。
（金曜日の10:00から仕事をして月曜日の15:00に作業終了するにあたり、休憩は12:00から13:00までの1時間で良いのかは別問題とし、追加の条件はないものとする。）
24時間毎に1時間の休憩があるわけだ。

条件を考えてみる。
・開始時間が13:00に開始して翌日の11:00に終了したら休憩は0回。
　開始時間が14:00に開始して翌日の13:00に終了したら休憩は1回。24時間に満たない場合、開始時間によって休憩回数は0回または1回。

・開始時間が12:00に開始して翌日の12:00に終了したら休憩は1回。何時に開始しても24時間では休憩回数は1回。

・開始時間が12:00に開始して翌日の13:00に終了したら休憩は2回。
　開始時間が13:00に開始して翌日の14:00に終了したら休憩は1回。
　開始時間が11:00に開始して翌日の12:00に終了したら休憩は1回。24時間＋1時間、は開始時間によって休憩回数は1回または2回。

経過時間を24で割って余りの時間と開始時間から判断できると言う事になり、
・24時間経過するごとに1回休憩時間が増える。
・（開始時間＋経過時間を24時間で割った余り時間）が13:00よりも大きければ休憩回数は1回増える。
と結論付けられる。

ならば、
=INT( ((C2+D2)-(A2+B2))/24 )
で24時間毎の休憩回数。
=MOD( (C2+D2)-(A2+B2),24 )
で経過時間を24時間で割った余りの数が求められる。
したがって、
=(経過時間)-(24時間毎の休憩時間)-(（開始時間＋経過時間を24時間で割った余り時間）が13:00よりも大き時1時間。小さい時は0時間）
の計算で作業時間を求められる。
数式は、
=(C2+D2)-(A2+B2)　-　INT( ((C2+D2)-(A2+B2))/24 )*"1:00"　-　IF(B2+MOD( (C2+D2)-(A2+B2),24 )＞"13:00","1:00",0)
こうなると思う（未検証）。


・・・
深夜作業のおける休憩時間を考慮する必要があれば、上記のように条件を考えて対処しよう。

No.2 回答者： 銀鱗 回答日時： 2016/04/11 22:35

1つ忘れていました。

表示を分単位というのは、24時間を1440分と表記したいということだろうか。
ならば表示形式をユーザー書式として
”[m]:s"
と、分に相当する部分を 「[　]」で囲めばよい。

分を最小単位として表記したいということであれば、
土曜に
"h:m"
とすればよい。
ついでに時間を上記のように
"[h]:m"
のようにすると24時間を越えても「77:00」のように表示することもできる。