エクセル不変則な休憩時間の実労働時間計算

お世話になります。色々と調べて見ましたが分からなかったので質問します。
今、勤務管理表を作成しています。労働条件は下記の通りです。
　◆定時：9：30から17：30　◆休憩時間：12：00～13：00
　◆遅刻した場合でも勤務時間が休憩の時間をまたいだときは必ず、12時から13時までとります。

遅刻したり、早退した場合、その時間によって、実労働時間が変わります。
例えば、12:53に出勤⇒13：59に退社したときは実労働時間は59分です。（休憩時間を考慮する）
また9：30よりも早く出勤しても特に手当てはつかず、9：30出勤とみなします。

この場合、下図の「出勤時間」、「退社時間」を入力すると、「休憩時間」と「実労働時間」が
自動的に出したいのですが、どの様な数式を使えばいいのでしょうか。
列を増やす事はOKです。よろしくお願い致します。


　　　

No.2 ベストアンサー 回答者： keithin 回答日時： 2011/04/14 23:10

ゾーンに切ってそれぞれの時間数を数えることで，同じ数式で計算できます。

添付図：
C2:
=MAX(MIN("12:0",B2),"9:30")-MIN("12:0",MAX("9:30",A2))
D2:
=MAX(MIN("17:30:0",B2),"13:00")-MIN("17:30",MAX("13:00",A2))
E2:
=MAX(MIN("22:00:0",B2),"17:30")-MIN("22:00",MAX("17:30",A2))
必要に応じて更に「深残帯(22時以降)」を計算する事も勿論できます。


F2:
=B2-A2
G2:
=SUM(C2:E2)
H2:
=F2-SUM(C2:E2)

9:30前の出社を「ノーカウント」とした辻褄として，H3で「休憩時間」に算定されている様子が示されています。

この回答への補足

keithin様

図解まで頂き、ありがとうございます！！
一度、午前と午後で区切って計算式をたてたのですが、うまくいかず。。。
回答を拝見して大変勉強になりました。

ただ、C2の計算式で"12:0"とD2の計算式で"17:30:0"と記載されていますが、
これは何か理由があるのでしょうか。

すみませんがご指導お願いします。

補足日時：2011/04/14 23:50

No.4 回答者： KURUMITO 回答日時： 2011/04/15 07:37

C2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(A2:B2)<>2,"",IF(B2>"12:00"*1,MIN(B2,"13:00")-MAX(A2,"12:00"),0))

D2セルには次の式を入力して下方にオートフィルドラッグしまう。

=IF(COUNT(A2:B2)<>2,"",B2-C2-MAX(A2,"9:30"))

なお、C列およびD列のセルの表示形式は時刻にします。
MAX(A2,"9:00")は9時30分より早く来たときは9時30分のシリアル値よりも小さくなりますので9時30分が計算に使われますし、9時30分よりも遅く出勤した場合には出勤した時刻が計算に使われることになります。
勤務時間の計算ではMINやMAX関数がよく使われます。意味を理解して使うようにすると便利ですね。

この回答への補足

KURUMITO様
おはようございます、回答を頂きましてありがとうございます！！
質問は締め切ってしまいますが、大変助かりました！！
min関数、MAX関数をもっと勉強します。。。

補足日時：2011/04/15 07:58

No.3 回答者： keithin 回答日時： 2011/04/15 00:36

ん？　失礼しました。

単純な編集ミスです。17:30も17:30:0も同じ17時30分(０秒)なので，計算には影響しません。

隣のセルの式を単純にコピーして，12を17:30にとか17:30を22:00に手直しして作成したので，ミスりました。

この回答への補足

keithin様
おはようございます。

あっ、よかったです。何か省略の意味なのかなぁと思い、考えていました。
図も載せて頂き、時間がかかりましたよね、お手数おかけいたしました。

この数式でやってみます！！！
本当にありがとうございました！助かりました！

補足日時：2011/04/15 08:02

No.1 回答者： kmetu 回答日時： 2011/04/14 22:54

C2に

=IF(A2>TIME(13,0,0),0,IF(A2>TIME(12,0,0),TIME(13,0,0)-A2,IF(B2<TIME(12,0,0),0,IF(B2<TIME(13,0,0),B2-TIME(12,0,0),TIME(1,0,0)))))

で

D2に

=IF(A2<TIME(9,30,0),B2-TIME(9,0,0)-C2,B2-A2-C2)

として両方とも必要なだけ下にフィルすればいかがでしょう。

この回答への補足

kmetu様
ご回答を頂き、ありがとうございます！！
今、頑張って回答を頂いた数式を確認しています。
時間計算は難しいですね…

補足日時：2011/04/14 23:43