一部計算式が判りません。ご指導をお願い致します

質問

ご存知の方、ご指導の程よろしくお願い致します。

先程、質問をさせていただき mshr1962 さん、mike_g さんから早速回答をいただきました。
ありがとうございました。
ただ、私の質問で説明不足の部分があり、それにより私の質問していることの回答としては、他のセルとの関係が不十分となり、再度、説明をさせていただきます。

早速、回答をいただきながら本当に申し訳ございません。

添付資料の説明をさせていただきます。
ある学校で職員の入校・退校・在校・在校(定時以降)の勤務状況調査票を作成しております。

以前もご指導をいただき調査票を作成し、利用して把握しておりましたが、新しい事象が発生しましたので再度追加質問になりました。

現在の条件及び関係のセルの公式を参考として列挙致しました。
添付資料の表は、下記の条件で作成したのですが

・入校時刻は、実際の入校した時刻を入力するのですが、08:15以降が計算される。

・退校時刻は、実際に退校した時刻を入力する。

・在校時間は、退校時刻から入校時刻を引いた時間を計算する。　【各行コピー】
　　公式：=IF(OR(D2="",E2=""),"",(E2-MAX(D2,"08:15")))

・在校時間(外)は、在校時間で定時16:45以降の在校時間を計算する。　【各行コピー】
　　公式：=IF(OR(C2={"×"}),F2,IF(E2>$I$3,MIN(E2,$I$5)-( $I$3),""))
　　　注：$I$3 から $I$5 まで作業用の表を作成し、非表示にしています。

・休日の列に、×(休日を示す)を表示(リスト使用)すると、休日出勤となり08:15以降の退校時刻までを在校時間(外)にも計算する。

質問事項
　５行目　４　火　 　は、G5 に 3:40 を求めたいのですが、4:55 になります。
　(定時の16:45 からの計算になっていると思われます。)

G5 に3:40 を求められる公式をご存知の方、ご指導をお願い致します。

回答No.3

CC_T · ベストアンサー

負の時間計算の結果でしょうね。
計算結果がエラー値になった場合の条件分岐を入れて消したり、
計算結果をテキストにして表示する方法などありますが、
ここはもう単純にF2セルの数式を、
=IF(E2>D2,E2-MAX(D2,"8:15"),"")
として、入校時間より退校時間の方が遅い場合のみ計算表示
するように変更しておきましょうか。

お礼

この度はご回答(私の求めるもの)をサンプル付きで、しかも３回にわたってご丁寧に説明・指導をいただき本当にありがとうございました。

質問の説明では的を得ておらず、又公式にも不要のものが入っており、公式を導くのに迷惑をおかけしました。
大変だったと思います。

OR の件ですが、以前は複数の条件(例えば：休日・祝日・代休等を表示するため)で休日を細分化しており、OR の削除忘れです。

正規に求める式が間違っていた理由が良く理解できました。

ゼロ値の表示はオフに設定して使用します。

勤務時間管理関係諸表は色々な条件がからみ、質問をする者も説明が難しいですし、ましてや回答をされる方も本当に大変のことと思っています。

本当にこの度は、私の求めたいことが出来ありがとうございました。
早速、業務に利用させていただきます。

今後ともよろしくお願い致します。

関数等に関しても大変にお詳しいようですので、大勢の質問者のためにも力になってあげて下さい。

最後に猛暑・酷暑の折からお身体にくれぐれも注意をされて下さい。

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

回答No.2

CC_T

訂正と修正です。

Ｆ２セル=E2-MAX(D2,"8:15")
Ｇ２セル=IF(C2="×",F2,IF(E2>$H$3,MIN($H$5,E2)-(MAX(D2,H$3,"8:15")),""))
を入れてみました。
これでどこか計算値がおかしい行残ってますかね？

補足

本日、午前中からサンプルを添付していただいての回答・説明ありがとうございました。

訂正と修正に基づき、確認をさせていただきました。

表自体は私の求めるもので利用させていただきたいと思います。

これを元に既存の勤務状況調査票(セルの幅の小さいセルの結合した表)の作成(修正)させていただきます。

ただ、作業中に
１　休日セルに×が無くて、入校・退校時刻を入力する前は在校時間にエラー(シャープのマーク)が表示されます。

２　休日セルに×が入力(休日扱い)されて、入校・退校時刻を入力する前は在校時間及び在校時間(外)にエラー(シャープのマーク)が表示されます。

このシャープのマークは、セルの幅より長い数値が入力されているか(列幅を広げましたが消えませんでした)、又は結果が負の値になる日付や時刻が入力されている。とありましたが、ERROR.値を表示させないようにするには、通常のIF 関数、ISERROR 関数で表示させないようにすればよろしいのでしょうか。

甘えついでに引き続きエラー(シャープのマーク)の処理をお願い出来れば、ありがたいです。

余分の質問事項を追加して、作業を増加させて申し訳ありません。

完成後にお礼を記載致しますが、補足入力の件本当によろしくお願い致します。

回答No.1

CC_T

=IF(OR(C2={"×"}),F2,IF(E2>$I$3,MIN(E2,$I$5)-( $I$3),""))
　　↑このorは機能していませんが、休日条件追加用なんでしょうかね？　
これは退社時間が"16:45"より遅ければ、"30:00"(I5)と退出時刻のうち早い方から"16:45"を引いた時間を入れる。
って内容ですから、現状Ｇ５は"4:55"で計算されるわけですね。

さて、目的としては、
(1)休日欄が×なら…勤務時間を全部時間外として計上
(2)休日欄が無なら…「16:45」と「出勤時刻」の遅い方から
　　　　　　　　　「退出時刻」と「$I$5」の早い方までの時間を計上
としたいということで宜しいでしょうか？

であれば、上記計算式は
=IF(C2="×",F2,MIN($I$5,E2)-(MAX(D2,I$3)))
で良いですね。

Excelのオプション設定でゼロ値の表示をオフに設定していない場合、残業ナシなら
"0:00"表示が出ますので、IF文の追加など応用を効かせてやってください。

・・・勤務時間計算ってやつは、まず休日・残業問題で引っかかって、次は中抜けや深夜割増時間への対応で悩んだりするものです(^^;

補足

ご回答をいただきありがとうございます。

確認チェックをさせていただきましたが、休日セルに×が付いている場合は問題ないのですが、無い場合で退校時刻が定時(16:45)より前の場合は、在校時間(外)にエラーの記号が表示されます。

もし、私の素人がI2からI5までのセルを作成しての作業で、別の良い方法がありましたら変更してご回答をいただければありがたいです。

よろしくお願い致します。
お礼は、完成後に送信させていただきます。