時間帯を切り取って合計時間を算出したい

下のカッコ内はエクセルのセル番地を示しています。
２行目に数値を入れて任意の時間帯に何時間あるか調べたいと考えています。下では前日22時から当日9時までの合計11時間あり、その内訳は時間帯Ａに9時間、時間帯Ｂに2時間ある、という計算です。
こういう計算を関数を使って表すことはできるでしょうか？


開始(B1)　終了(C1)　合計(D1) 時間帯Ａ(E1)　時間帯Ｂ(F1)
22:00(B2) 9:00(C2) 11:00(D2) 9:00(E2) 2:00(F2)

※時間帯Ａ=前日22:00～当日7:00
　時間帯Ｂ=当日7:00～12:00

No.4 ベストアンサー 回答者： don9don9 回答日時： 2010/03/10 12:07

例えば開始が6:00、終了が23:00であった場合は

E2は6:00～7:00の1時間と22:00～23:00の1時間を足して2時間
F2は7:00～22:00の15時間
でいいのですよね？

D2は
=IF(B2>C2,C2+1-B2,C2-B2)

E2はちょっと複雑ですが
=IF(AND(ISBLANK(B2)=FALSE,ISBLANK(C2)=FALSE),IF(C2<B2,IF(C2>"7:00"*1,"7:00"*1+1,C2+1)-IF(B2<"22:00"*1,"22:00"*1,B2)+IF(B2<"7:00"*1,"7:00"*1-B2,"0:00"*1),IF(B2<"7:00"*1,IF(C2<"7:00"*1,C2,"7:00"*1)-B2,"0:00"*1)+IF(C2>"22:00"*1,C2-"22:00"*1,"0:00"*1)),"0:00"*1)

E2の式の意味は以下の通りです。

開始時刻＞終了時刻となった場合（0:00をまたいだ場合）
終了時刻＋1日と開始時刻の差を求め、その内の22:00～7:00の間の時間数を算出

上記以外の場合（開始時刻≦終了時刻、0:00をまたがない場合）
開始時刻が7:00以前、終了時刻も7:00以前の場合、開始時刻から終了時刻までの時間数
開始時刻が7:00以前、終了時刻が7:00以降の場合、開始時刻から7:00までの時間数
終了時刻が22:00以降の場合、22:00から終了時刻までの時間数
これを合算したもの

F2は
=D2-E2

この回答へのお礼

ありがとうございました。
わたしにはちょっと複雑ですが、教えていただいたとおりいれたらできました。

お礼日時：2010/03/14 18:34

No.3 回答者： spring57 回答日時： 2010/03/10 12:07

Ｄ２のセルに=(24-B2)+C2の関数式を

Ｅ２のセルに=C2を
Ｆ２のセルに=(24-B2)の関数式を入力して見て下さい。

この回答へのお礼

ありがとうございました。

お礼日時：2010/03/14 18:32

No.2 回答者： KURUMITO 回答日時： 2010/03/10 11:41

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

=IF(COUNT(B2:C2)<>2,"",IF(B2>"12:00"*1,C2+"24:00"-B2,C2-B2))

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

=IF(COUNT(B2:C2)<>2,"",IF(B2>"12:00"*1,MIN("7:00"+"24:00",C2+"24:00")-MAX("22:00",B1),MIN("7:00",C2)-B2))

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

=IF(COUNT(B2:C2)<>2,"",IF(B2>"12:00"*1,MIN("12:00"+"24:00",C2+"24:00")-MAX("7:00"+"24:00",B1),MIN("12:00",C2)-MAX("7:00",B1)))

Ｄ２セルからＦ２セルまでの範囲を選択し下方にオートフィルドラッグします。
ところでＤ２セルの式ですが例えば開始が21:00で終了が9:00のときはＤ２セルは12:00となるのに対してＥ２セルは9:00、Ｆ２セルは2:00となります。これは時間帯Ａの最大値が9:00であるためです。Ｄ２値がＥ２とＦ２の合計にするのでしたら、Ｄ２への入力の式は次のようになりますね。

=IF(COUNT(B2:C2)<>2,"",E2+F2)

この回答へのお礼

ありがとうございました。
うまくいきました。関ここまでの関数はかなり勉強しないと難しいですね。

お礼日時：2010/03/14 18:36

No.1 回答者： DIooggooID 回答日時： 2010/03/10 10:45

Ｂ１セルには、前日の時刻が設定され、

Ｃ１セルには、当日の時刻が、　必ず設定されるのですか？

この回答へのお礼

こんにちは、早速ありがとうございます。
B1、C1セルですが、前日、当日はまちまちです。左の値が右より大きい時は左は前日というようにできればと考えています。シリアル値ですので、こういったことは無理なのでしょうか・・・

お礼日時：2010/03/10 11:03