EXCELでの時間計算(休憩時間の差引)について

質問

パートさんの遅刻・早退・外出時間を自動計算する表を作っていますがうまく作れません・・・
下記の場合どうしたらよいでしょうか？

例えば遅刻の合計時間を算出するのに開始時刻から終了時刻を引き算しますが
10:20～10:30、 12:00～12:40、 14:50～15:00、は休憩時間なので自動的
に差し引きたいと考えています。
遅刻の時間内に、休憩時間である10:20～10:30が含まれれば10、
12:00～12:40が含まれれば40、14:50～15:00が含まれれば10を引くという感じです。

どのような計算式にすればよいのでしょうか？
ちなみに勤務時間は8：30～17：00です。

例えば、
(例）セル【B2】8:30(開始）セル【B3】13:00（終了）の場合、セル【B4】には50分を差引いた
3：40の合計時間を算出したいです。

色々調べて、B4に以下の式を入力してみましたが、どうもうまくいきません。

=IF(B3<>"",B3-B2-IF(TEXT(B3,"hh:mm")>"15:00","1:00",IF(TEXT(B3,"hh:mm")>"12:40","0:40",IF(TEXT(B3,"hh:mm")>"10:30","0:10","0:00"))))

どうぞ宜しくお願いいたします。

回答No.2

nanase_p_q_p_q · ベストアンサー

こんばんは。

補足いただきありがとうございます。

認識といたしまして、B2およびB3には、遅刻、早退、外出問わず、
記入されているものと考えてよろしいですね。

式が長くなりますが以下のようにしてみるのはいかがでしょうか。

《訂正後》
=IF(B3<>"",B3-B2-IF(OR(TEXT(B2,"hh:mm")>="10:30",TEXT(B3,"hh:mm")<="10:20"),"0:00",IF(TEXT(B3,"hh:mm")>="10:30","10:30",TEXT(B3,"hh:mm"))-IF(TEXT(B2,"hh:mm")<="10:20","10:20",TEXT(B2,"hh:mm")))-IF(OR(TEXT(B2,"hh:mm")>="12:40",TEXT(B3,"hh:mm")<="12:00"),"0:00",IF(TEXT(B3,"hh:mm")>="12:40","12:40",TEXT(B3,"hh:mm"))-IF(TEXT(B2,"hh:mm")<="12:00","12:00",TEXT(B2,"hh:mm")))-IF(OR(TEXT(B2,"hh:mm")>="15:00",TEXT(B3,"hh:mm")<="14:50"),"0:00",IF(TEXT(B3,"hh:mm")>="15:00","15:00",TEXT(B3,"hh:mm"))-IF(TEXT(B2,"hh:mm")<="14:50","14:50",TEXT(B2,"hh:mm"))),"")

●検証
B2："8:30"、B3："10:00"の場合　⇒　1:30　
B2："8:30"、B3："10:20"の場合　⇒　1:50
B2："8:30"、B3："10:25"の場合　⇒　1:50(5分が差し引かれている)
B2："8:30"、B3："10:30"の場合　⇒　1:50(10分が差し引かれている)
B2："8:30"、B3："12:00"の場合　⇒　3:20(10分が差し引かれている)
B2："8:30"、B3："12:40"の場合　⇒　3:20(50分が差し引かれている)
B2："14:00"、B3："15:00"の場合　⇒　0:50(10分が差し引かれている)
B2："14:00"、B3："17:00"の場合　⇒　2:50(10分が差し引かれている)

以上です。

●式について
以下のような構成になっています。

=IF(B3<>"", B3-B2- [後述の(1)] - [後述の(2)] - [後述の(3)], "")

(1)10:20～10:30の間の休憩時間(実時間)
(2)12:00～12:40の間の休憩時間(実時間)
(3)14:50～15:00の間の休憩時間(実時間)

いかがでしょうか。

お礼

早々の回答ありがとうございました。
おかげさまで、先ほど表が完成しました！

こんな長い式になるとは…
もともと入力していた式じゃだめなはずですね。
式の構成についても理解できました。

お礼が遅くなりましたが、本当に本当にありがとうございました！！

回答No.1

nanase_p_q_p_q

以下の点を修正するだけで要件を満たすことができると思います。

《修正前》
=IF(B3<>"",B3-B2-IF(TEXT(B3,"hh:mm")>"15:00","1:00",IF(TEXT(B3,"hh:mm")>"12:40","0:40",IF(TEXT(B3,"hh:mm")>"10:30","0:10","0:00"))))

《修正後》
=IF(B3<>"",B3-B2-IF(TEXT(B3,"hh:mm")>"15:00","1:00",IF(TEXT(B3,"hh:mm")>"12:40","0:50",IF(TEXT(B3,"hh:mm")>"10:30","0:10","0:00"))))

変更した箇所は1箇所だけです。

"0:40"を"0:50"に変更しました。

※以下、蛇足ですが、以下の点は考慮に入れる必要はありますか？
(1)B3に"12:20"が入力される。
　　⇒現行の式では10のみ引かれます。

(2)B3に"15:00"が入力される。
　　⇒現行の式では50のみ引かれます。

この要件を考慮に入れるともう少し複雑な式になります。

補足

早々の回答ありがとうございます！

そうなんです。
勤務時間8：30～17：00の間に発生した遅刻や早退、外出時間を
自動計算させたいのですが、

【遅刻】
○【12：00出社の場合】8:30～12:00＝計3：20
○【12：40出社の場合】8:30～12:40＝計3：20

共に勤務時間7：30のうち、3：20の遅刻となるはずが
下の段、12：40出社の場合、計が”4：00”となってしまうんです。
同様に外出した場合も、

【外出】
○【16:00から１時間外出した場合】16:00～17:00＝計1：00

としたいのですが、現行の式ですと16:00～17:00＝計0：00
となってしまうのです。

細々とすみません。どうぞ宜しくお願いいたします。