エクセルで休憩時間算出

エクセル初心者です。
エクセルで、休憩時間、稼働時間の算出をしたいと思っています。
作業開始時刻、作業終了時刻が入力値で、稼働時間、休憩時間、稼働時間を求めたいです。

休憩時間は、稼働時間が４時間毎に３０分休憩、　１２時から１３時にまたぐ場合は、１時間休憩で計算したいですが、どうもこの部分が思いつきません。
アドバイスお願いします。

No.6 回答者： don9don9 回答日時： 2012/07/03 09:35

No.5の回答の最後は

×本件では稼働時間数（昼休み除く）を4時間単位で切り捨てて、それに30分をかけることで休憩時間数を計算しています。

○本件では稼働時間数（昼休み除く）を4時間単位で切り捨てて、それを4時間で割って30分をかけることで休憩時間数を計算しています。

の誤りです。失礼しました。

No.5 回答者： don9don9 回答日時： 2012/07/03 09:18

No.2,3です。

仮にA1が開始時刻、B1が終了時刻だとすると
=IF(COUNT(A1:B1)=2,MIN("13:00",MAX("12:00",B1))-MAX("12:00",MIN("13:00",A1)),"0:00"*1)
という式で、A1の時刻～B1の時刻と12:00～13:00とで重なっている時間数が出せます。
（※夜勤などで稼働時間が日をまたぐことはないという前提です）
これを踏まえて、以下のような式でどうでしょうか。

＜昼休みを挟んだら稼働時間がリセットされる場合＞
時間数を午前・昼休み・午後と分けて計算します。
A列…開始時刻
B列…終了時刻
C列…午前（0:00～12:00）の稼働時間数（作業列）
D列…昼休み（12:00～13:00）の時間数（作業列）
E列…午後（13:00～24:00）の稼働時間数（作業列）
F列…稼働時間数
G列…休憩時間数
とします。

C1の式
=IF(COUNT(A1:B1)=2,MIN("12:00",MAX("0:00",B1))-MAX("0:00",MIN("12:00",A1)),"0:00"*1)
D1の式
=IF(COUNT(A1:B1)=2,MIN("13:00",MAX("12:00",B1))-MAX("12:00",MIN("13:00",A1)),"0:00"*1)
E1の式
=IF(COUNT(A1:B1)=2,MIN("24:00",MAX("13:00",B1))-MAX("13:00",MIN("24:00",A1)),"0:00"*1)
F1の式
=IF(COUNT(A1:B1)=2,B1-A1-G1,"0:00"*1)
G1の式
=FLOOR(C1,"4:00")/"4:00"*"0:30"+D1+FLOOR(E1,"4:00")/"4:00"*"0:30"

＜昼休みを挟んでも稼働時間がリセットされない場合＞
単純に終了時刻と開始時刻の差分の時間数を計算します。
A列…開始時刻
B列…終了時刻
C列…昼休みの時間数（作業列）
D列…稼働時間数
E列…休憩時間数
とします。

C1の式
=IF(COUNT(A1:B1)=2,MIN("13:00",MAX("12:00",B1))-MAX("12:00",MIN("13:00",A1)),"0:00"*1)
D1の式
=IF(COUNT(A1:B1)=2,B1-A1-C1,"0:00"*1)
E1の式
=C1+FLOOR(D1,"4:00")/"4:00"*"0:30"

※式についての補足
多くの式で
=IF(COUNT(A1:B1)=2,～
としているのは、A1とB1が両方入力された場合のみ計算するようにするためです。
COUNT関数は、数値が入力されているセルの個数を返す関数です。
A1とB1に正しく時刻データが入力されていない（未入力あるいは文字列などが入力されている）場合、0:00を返すようにしています。

FLOOR関数は、指定した値の倍数の近似値に切り捨てる関数です。
=FLOOR(時間数,"0:15")
などと指定すると15分単位で切り捨てることができます。
本件では稼働時間数（昼休み除く）を4時間単位で切り捨てて、それに30分をかけることで休憩時間数を計算しています。

No.4 回答者： KURUMITO 回答日時： 2012/07/02 18:02

回答No1です。

午前中でも4時間ごとに30分の休憩を入れるのでしたらE2セルには次の式を入力してください。

=IF(D2="","",IF(B2<="13:00"*1,MIN(C2,"13:00")-MAX(B2,"12:00"),0)+IF(B2<"8:00"*1,INT((MIN(C2,"12:00")-B2)/"4:00")*"0:30",0)+IF(C2>"16:00"*1,INT((C2-MAX(B2,"13:00"))/"4:00")*"0:30",0))

いずれのセルもセルの表示形式は時刻から選択します。休憩時間は勿論昼休みの時間が入っています。

No.3 回答者： don9don9 回答日時： 2012/07/02 17:57

No.2です。

補足ありがとうございます。
しかし新たに疑問がでてきました。

>・4時間のうち30分が休憩時間（稼働3時間30分＋休憩30分）」です。
>・4時間毎の30分休憩と昼休みが連続することはあります。

この通り解釈すれば「開始が8:00、終了が18:00」の場合は「8:00～12:00の4時間のうち30分が休憩、12:00～13:00が昼休み、13:00～18:00の5時間の間にもう30分休憩」となり

>・「開始が8:00、終了が18:00」の場合、昼休みを除くトータル稼働時間は9時間ですが、それに対して発生する休憩時間は30分です。

休憩時間は30分ではなく1時間になるのではないでしょうか？
それとも昼休みを挟んだら稼働時間が一度リセットされるのでしょうか？
例えば10:00から2時間稼働した後、昼休みとなった場合は、次に休憩が入るのは何時からですか？

No.2 回答者： don9don9 回答日時： 2012/07/02 16:46

>４時間毎に３０分休憩、　１２時から１３時にまたぐ場合は、１時間休憩

「4時間毎に30分の休憩」というのは「4時間働いたら30分休憩を与える（稼働4時間＋休憩30分）」なのか「4時間のうち30分が休憩時間（稼働3時間30分＋休憩30分）」なのか？

12:00～13:00というのはおそらく昼休みだと思いますが、昼休みはこの時間帯で固定なのか？
その場合、作業開始か作業終了がその時間帯と重なった場合どうなるのか？
例えば「開始が12:30、終了が21:00」「開始が7:00、終了が12:45」などの場合は休憩時間は何時間何分になるのか？

昼休みは、稼動が4時間に満たなくても与えられるのか？
例えば「開始が10:00、終了が14:00」の場合は稼働3時間＋休憩1時間となるのか？

4時間の稼働に対する30分の休憩と昼休みは相殺されるのか？
例えば「開始が8:00、終了が18:00」の場合、昼休みを除くトータル稼働時間は9時間ですが、それに対して発生する休憩時間は1時間か？30分か？
（午前中の4時間に対する休憩は昼休みで相殺されるのであれば、午後の5時間に対する30分のみということになりますよね？）

4時間毎の30分休憩と昼休みが連続することはあるのか？
例えば「開始が7:30、終了が16:00」の場合、11:30で開始から4時間経過になるが、その場合30分休憩の後そのまま昼休み突入するのか？

補足いただけませんか。
不明瞭な要素が多く、現段階では式が組み立てにくいです。

この回答への補足

言葉足らずですみません。補足いたします。

・4時間のうち30分が休憩時間（稼働3時間30分＋休憩30分）」です。
・12:00～13:00昼休み固定です。

・例えば「開始が12:30、終了が21:00」の時は、お昼休み分　３０分＋４時間毎の休憩２回で１時間３０分休憩
・「開始が7:00、終了が12:45」などの場合は休憩時間は３０分＋昼休み分４５分
・「開始が8:00、終了が18:00」の場合、昼休みを除くトータル稼働時間は9時間ですが、それに対して発生する休憩時間は30分です。
・4時間毎の30分休憩と昼休みが連続することはあります。

よろしくお願いします。

補足日時：2012/07/02 17:17

No.1 回答者： KURUMITO 回答日時： 2012/07/02 15:23

B2セルに作業開始時刻、C2セルに作業終了時刻、D2セルに稼働時間、E2セルに休憩時間とすればD2セルには次の式を入力します。

=IF(COUNT(B2:C2)<>2,"",C2-B2)

E2セルには次の式を入力します。

=IF(D2="","",IF(B2<="13:00"*1,MIN(C2,"13:00")-MAX(B2,"12:00"),0)+IF(C2>"16:00"*1,INT((C2-MAX(B2,"13:00"))/"4:00")*"0:30",0))

ここで13時以前の作業開始では最高1時間までの休憩時間となりますが、4時間ごとに30分の休憩時間は13時以降の勤務時間について4時間ごとに30分の休憩時間となっています。