【エクセル】休息時間の計算

休息時間の計算をしていて以下のようなものを計算したいのですが、
その最終目標の計算式に到達することができません。
参考に画像を添付いたします。ご教授ください。

--------------------------------------------

表示したいのは「休息時間」の部分です。

１）前日の終業時刻、当日の始業時刻の両方が空欄の場合
→「休」と表示
２）前日の終業時刻が空欄で当日の始業時刻に値が入力されてる場合
→24時間＋（当日0：00から当日始業時刻までの時間）　の時間
３）当日の始業時刻が空欄で前日の終業時刻に値が入力されてる場合
→24時間＋（前日終業時刻から当日0：00までの時間）　の時間
４）前日の終業時刻、当日の始業時刻の両方とも値が入力されている場合
→（当日の始業時刻）－（前日の終業時刻）の時間


２）と３）をどうしたら良いかわからないので、
現在暫定的に２）か３）に該当する場合は「24時間以上」と表示するようにしており、
例えばセルE3には以下のような計算式を入れております。

=IF(AND($D2="",$C3=""),"休",IF(OR($D2="",$C3=""),"24時間以上",$C3-$D2))

以上　宜しく御願い申し上げます。

No.4 ベストアンサー 回答者： keithin 回答日時： 2011/04/02 22:06

＞前日出勤が無いので3月3日はまるまる休み扱いとなり、

＞23：59迄前日の休息時間とカウントされている為、
＞ここは3月4日0：00から17：00迄の時間を表示させたい。

だとすると，また辻褄が合わなくなっています。
3/8についてですが，3/7が空白-空白で本来の意味で「休」になっており，同じように「(3/7の)23:59まで全日の休息時間とカウントされている」ため，3/8は同様に0時～始業時までのカウントで良くなります。

つまり元のご相談の状況提示で
「２）前日の終業時刻が空欄で当日の始業時刻に値が入力されてる場合
　→24時間＋（当日0：00から当日始業時刻までの時間）　の時間」

の部分は全て，24時間を足さずに「0時～始業時刻まで」だけの計算で良いハズです。


その前提で添付図は
E3:
=IF(C3="",IF(D2<=A3-"8:0","休",A3-D2+1),C3-IF(D2="",A3,D2))

この回答への補足

お礼で補足内容をつけてしまいました。すみません。

補足入力ですべきでした。
ちなみに私がお礼部分に記載した式（keithinさんの式につけたした部分）は
終業時刻が翌日0：00を超えない場合、終業時刻から翌日0：00迄の時刻を加えたものにしたつもりです。

補足日時：2011/04/03 12:48

この回答へのお礼

またまたご回答くださり、本当に感謝いたします。
そして、再度のご指摘ありがとうございます。
いただいたこの回答とご指摘いただいた内容を読み、もう一度考え直してみました。

そしてご指摘を踏まえたうえでkeithinさんのご回答を参考にしながら
以下のような式を当てはめたところ、恐らく正しく表示したかったはずの形になったように思います。

E3:
=IF(C3="",IF(D2<=A3-"8:0","休",A3-D2+1),C3-IF(D2="",A3,D2)+IF(C4="",IF(D3<A4,A4-D3)))

これで辻褄も合うようになったと思うのですが、どうでしょうか？

何度も続いて申し訳ないので、お時間がある時にご覧いただければ幸いです。

お礼日時：2011/04/03 12:36

No.3 回答者： keithin 回答日時： 2011/04/02 15:23

3/4の終業のように翌日にまたいでいる場合，3/5の「当日ゼロ時との差」は「－１時間」なので少なくとも「24時間以上」ではなくなります。

それに伴い「当日」を確定するため（特にＣ列にデータが無い場合），Ａ列にきちんと年/月/日形式で日付が記入してあるものとします。


そういう前提でご質問に書かれているとおりに計算すると
E3:
=CHOOSE(COUNT(D2,C3)+1,"休",MOD(C3-D2,1)+(A3>D2),C3-D2)
のようになります。

Ｅ列はセルの書式設定の表示形式のユーザー定義で
[h]:mm
と設定します。



＃１日休みを挟むと24時間がダブルカウントされるような気がしてなりませんが。

この回答への補足

回答ありがとうございます。

>１日休みを挟むと24時間がダブルカウントされるような気がしてなりませんが。

本当ですね！大変すみません。
そうなるとそもそも質問がおかしな状態だということに気づきました。

添付画像のエクセルに実際式を当て込むと実際大きなミスが自分にあったことがわかりました。

質問を補足・修正させてください・・・；

休息時間は24時間+8時間以上で「休」と見なされるのでそのように表示させたいのです。
ですが、そういう計算ができるかも、という事すら頭に閃かなかったので最初の質問では
時間をそのまま表示させたい、と申し上げておりました。

画像が添付できないので、見苦しいですがテキスト表示いたします。


A C　　　　　　　　　　　D　　　　　　　　　　E
1　　　　日付　　　　　　始業時刻　　　　　　　　 終業時刻　　　　　　休息時間
2　　　　3月1日　　　　2011/3/1 8:00　　　　2011/3/1 17:00　　　15時間00分
3　　　　3月2日　　　　2011/3/2 6:00　　　　2011/3/2 13:00　　　13時間00分
4　　　　3月3日　　　　　　　　　　　　　　　　　　　　　　　　　　　　　35時間00分
5　　　　3月4日　　　　2011/3/4 17:00　　　　2011/3/5 1:00　　　41時間00分
6　　　　3月5日　　　　　　　　　　　　　　　　　　　　　　　　　　　　　23時間00分
7　　　　3月6日　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　休
8　　　　3月7日　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　休
9　　　　3月8日　　　　2011/3/8 8:00　　　　2011/3/8 16:00　　　32時間00分

先ほどご回答くださった計算式を当てはめましたら
確かに、この場合ですとE5の時間がかぶってしまっておりおかしかったです。

■本来はE4のセルは24時間+8時間=34時間以上なのでE7、E8同様「休」と表示させたいです。
■E5は、私が申し上げてた質問では時間が被ってしまっておりますが、
前日出勤が無いので3月3日はまるまる休み扱いとなり、23：59迄前日の休息時間とカウントされている為、
ここは3月4日0：00から17：00迄の時間を表示させたい。


これが正しく表示させたい内容です。

せっかくお時間を割いてご回答くださったのに失礼いたしました。

補足日時：2011/04/02 16:09

No.2 回答者： web2525 回答日時： 2011/04/02 14:26

３）の計算式ですが

=2-$D2-INT($D2)⇒=2-（$D2-INT($D2)）

$D2-INT($D2)を先に計算する形にしないと負の日時となり表示出来ませんでした

校正いたします

この回答へのお礼

早速の回答ありがとうございます。
keithin　さんがご指摘くださったことで自分の質問にそもそも大きな欠陥があったことに気づきました。

web2525さんの回答も後学のために後からじっくり復習しようと思います。

お礼日時：2011/04/02 15:46

No.1 回答者： web2525 回答日時： 2011/04/02 14:20

24時間以上の表示の場合セルの書式設定で[h]のように括弧で囲います

(この括弧の名称は何だっけ?)

計算式は
２）前日の終業時刻が空欄で当日の始業時刻に値が入力されてる場合
→24時間＋（当日0：00から当日始業時刻までの時間）　の時間
の場合は
=$C3-INT($C3)+1

３）当日の始業時刻が空欄で前日の終業時刻に値が入力されてる場合
→24時間＋（前日終業時刻から当日0：00までの時間）　の時間
の場合は
=2-$D2-INT($D2)

※共にE3セルに計算式が有る場合

セルの表示形式を
[h]"時間"mm"分"
に設定