エクセル 時間計算 複数の条件

エクセルド素人が苦戦しています。。。
どなたかお力をお貸しください。

出勤時刻 外出時刻 戻り時刻 退勤時刻 所定時間 残業時間 深夜所定 深夜残業
9:58 　　　16:00 17:00 　22:31 　　8　　　　　3 　 　　　　0.5　　

所定時間は出勤時間から5:00~22:00の間の勤務時間引くことのもしこの時間内に休憩をとった場合
休憩時間、8時間を超える場合はマイナス8（所定時間が8時間を超えることはない）
残業時間は8時間―マイナス8する前の所定時間
深夜所定は22:00～5:00の間の勤務時間引くことのもしこの時間内に休憩をとった場合休憩時間
（すでに所定時間が8時間を超えている場合0）
深夜残業は残りの時間

時間は15分切り捨て

全部を一括にかなえられる計算式はないと思っていますが
ひとつひとつの計算式でもお手上げ状態です。。。
すみません、宜しくお願いします！

No.1 回答者： 銀鱗 回答日時： 2015/10/15 16:38

お昼休憩開始時間と退社時間を比較、小さい方の時間を採用。

お昼開始時間と、定時出勤時間と出社時間を比較した大きい方の時間を比較し、小さい方の時間を採用。
引いて午前中の就業時間を求める。
＝MIN("12:00",退社時間)-MIN("12:00",MAX("8:00",出社時間))

定時退社時間と退社時間を比較し、小さい方の時間を採用。
お昼終了時間と、定時退社時間と出社時間を比較した大きい方の時間を比較し、小さい方の時間を採用。
引いて午後の就業時間を求める。
＝MIN("17:00"-退社時間)-MIN("17:00",MAX("13:00",出社時間))

定時の所定時間においては上記の式を足すことで就業時間を求められる。
＝MIN("12:00",退社時間)-MIN("12:00",MAX("8:00",出社時間))+MIN("17:00"-退社時間)-MIN("17:00",MAX("13:00",出社時間))

同様に、残業時間と深夜残業時間を求めればよい。
MIN関数とMAX関数が何をしているのかを理解できれば難しいことではない。
是非チャレンジしてみよう。


上に示した計算式では午前中に早退した場合エラーになります。午前中に早退した場合を考慮して所定時間の計算を修正してみてください。
また、早朝残業の計算もやってみましょう。
（理解できて初めて問題解決と自分は考えているので、宿題的な回答になることをお詫びします）

この回答へのお礼

ありがとうございます。。。
まさかこんなまとまっていない質問にこんなに早くご回答いただけると
思っていませんでした。。。。。
（理解できて初めて問題解決と自分は考えているので、宿題的な回答になることをお詫びします）
いえいえ、とんでもないです。
素人ながら、きっとできるに違いないと、挑戦に近い感覚でいましたので
なんとしても、解決したいと思っています！

とはいえ、まだご回答も読み込めていない次第なので、じっくりと
解読させていただきます。

取り急ぎ、お礼を申し上げます。
ありがとうございます！

がんばります！笑

お礼日時：2015/10/15 17:02