パート用 労働時間の時間外計算をエクセルでしたいのですが・・・

質問

エクセルで、パートタイム用の時間計算表を作成しております。

単純にタイムカードの様式をエクセルで作成し、項目毎に時間を入力し労働時間を算出することが目的です。※賃金の計算は不要。

私が作成した項目は、一行に

出勤(B2) ・ 時間内退勤(C2) ・ 時間内出勤(D2) ・ 退勤(E2) ・ その他(F2、G2) ・ 労働時間小計(H2,I2) ・ 時間外(J2)　です。
※時間内退勤・出勤はお昼休みに該当します。

労働時間小計欄、Ｈ２にはＣ２－Ｂ２を入れ、Ｉ２にはＥ２－Ｄ２を設定しました。

問題は時間外計算です。

時間外対象の条件ですが、基本労働時間　８時から１７時を基準とし、
１．出勤→８時前に出勤した場合の時間差。
２．退勤→１７時以降の時間差。
３．上記条件に該当しても、それぞれ５分未満の場合は対象外。

例；　出勤7:30　　退勤17:15　　　時間外(J2)　0:45
　　　 出勤7:56　　退勤17:30　　　時間外(J2)　0:30

このように、Ｊ２欄に出勤・退勤の時間外合計時間が表示されるようにしたいのですが、何か良い方法があれば教えて下さい。対象外の場合、空欄になるように設定したいと考えております。

補足：深夜24時を過ぎる事はありません。
　　　時間の入力方法は１つのセル内に8:00というように入力します。

回答No.4

pkh4989 · ベストアンサー

ANo.1 です。
TIME(0,5,0)にすると 5分が該当されないので、TIME(0,4,59)にしてください。
=IF(IF($B$1-B2<TIME(0,4,59),0,$B$1-B2)+(IF(E2-$E$1<TIME(0,4,59),0,E2-$E$1))<TIME(0,4,59),"",IF($B$1-B2<TIME(0,4,59),0,$B$1-B2)+(IF(E2-$E$1<TIME(0,4,59),0,E2-$E$1)))

お礼

迅速な回答　ありがとうございましたm(_ _)m

とても参考になりました。
何日も自分なりに考えましたが、初心者の私にとって難題でした。
早速使わせてもらいます。

貴重なお時間をいただき感謝致します。ありがとうございましたm(_ _)m

回答No.3

pkh4989

ANo.1 です。
返事が遅くなりました。
ごめんなさい。再度訂正します。
(3) 時間外セル(J2)の数式を以下のようにしてみてください。
=IF(IF($B$1-B2<TIME(0,5,0),0,$B$1-B2)+(IF(E2-$E$1<TIME(0,5,0),0,E2-$E$1))<TIME(0,5,0),"",IF(B$1-B2<TIME(0,5,0),0,$B$1-B2)+(IF(E2-$E$1<TIME(0,5,0),0,E2-$E$1)))

あくまでも、参考意見なので間違ったら、ごめんなさい。m(__)m

回答No.2

pkh4989

ANo.1 です。
すみません。
数式設定が間違いましたので訂正します。
(3) 時間外セル(J2)の数式を以下のようにしてみてください。
=IF(IF(MINUTE($B$1-B2)<5,0,$B$1-B2)+(IF(MINUTE(E2-$E$1)<5,0,E3-$E$1))<TIME(0,5,0),"",IF(MINUTE($B$1-B2)<5,0,$B$1-B2)+(IF(MINUTE(E3-$E$1)<5,0,E2-$E$1)))

補足

お礼が遅くなり申し訳ありません。
早速使わせて頂きました。

再度教えて頂きたいことがあり、補足させていただきます。

例；出勤時間（Ｂ２）７：００　　退勤時間（Ｅ２）１８：００

と入力した時に、時間外計算が反映されないようです。分の部分が００になった場合、反映されないようです。

次に、出勤（Ｂ２）に関しましては　００のみ反映されないのですが、退勤（Ｅ２）に関しましては、００から０５まで入力した場合も反映されないようです。

例；退勤→１８：００，１８：０１～１８：０５

エクセル初心者の私には問題が解けません・・・・

もし、良い方法があれば教えていただけないでしょうか？
宜しくお願いします。

回答No.1

pkh4989

(1) セル(B1)に出勤基準時間 -> 8:00 を入力 (入力セルはどこでも結構です)
(2) セル(E1)に退勤基準時間 -> 17:00 を入力 (入力セルはどこでも結構です)
(3) 時間外セル(J2)に以下の数式を設定してください。
=IF(($B$1-B2)+(E2-$E$1)<TIME(0,5,0),"",($B$1-B2) + (E2-$E$1))

<参考>
http://www.eurus.dti.ne.jp/~yoneyama/Excel/nyumon/kinmu/index.html