excelで出勤簿を作成しています。

質問

excelで出勤簿を作成しています。

17:30～22:00までが残業帯で
22:00～5:00までが深夜帯です。

17:30前から連続勤務で深夜帯までいくと
どうしても深夜帯も残業に入ってしまいます。

例
8:00～25:00 残業 7.5 深夜 3になってしまいます。

残業 4.5 深夜 3とでるようにしたいのですがどうしたらいいのでしょうか？

21:00あたりから夜勤を入れると
17:30からの残業が計算されてしまいます。

例
21:00～25:00 残業 3.5 深夜 3 になってしまいます。

残業 1 深夜 3とでるようにしたいのですがどうしたらいいのでしょうか ？

回答No.3

KURUMITO · ベストアンサー

例えばA１セルに出勤時間、B１セルに退勤時間があるとしたら残業時間は次の式で計算できます。
なお、退勤時間などの入力は０時を過ぎた場合には例えば１時でしたら25:00のように入力されているとします。

=IF(COUNT(A1:B1)<>2,"",IF(AND(A1<"22:00"*1,B1>"17:30"*1),(MIN(B1,"22:00")-MAX("17:30",A1))*24,""))

深夜時間は次の式で計算できます。

=IF(COUNT(A1:B1)<>2,"",IF(B1>"22:00"*1,(MAX(B1,"22:00")-MAX("22:00",A1))*24,""))

なお、セルの表示形式は標準にします。

時間の計算では"22:00"+"1:00"などはエクセルが自動的にシリアル値と評価して計算できますが、B1>"22:00"*1のように時刻を比較などの式で使用する場合には"22:00"をシリアル値と評価されるように*1を行っています。
また、3:30を3.5時間のようにするためには時間の計算で求められた時間(シリアル値)に24を掛けています。シリアル値は日にち単位となっているため24を掛けて時間単位にしているのです。
勤務時間の計算ではMINやMAX関数が多く使われますので十分理解できるようにしてください。

お礼

とても参考になりました。
ありがとうございました。

回答No.2

mu2011

既に回答済みですので参考まで。
>17:30前から連続勤務で深夜帯までいくとどうしても深夜帯も残業に入ってしまいます。
　⇒この記載の意図がよく分かりませんが、所定勤務時間を越えた時間が残業時間です。
　　深夜時間は２２：００～翌５：００に勤務した時間です。

>21:00～25:00 残業 3.5 深夜 3 になってしまいます。
　⇒この場合、残業時間は０、深夜３ということになります。

>8:00～25:00 残業 7.5 深夜 3になってしまいます。
>残業 4.5 深夜 3とでるようにしたいのですがどうしたらいいのでしょうか？
　⇒深夜と普通残業時間が重複しているのだから、残業時間＝残業時間－深夜時間としてもよいのではないでしょうか。

因みにこの時間計算は、時間割増に関係すると思いますのでもう少し労働基準法（Ｈ２２年に改訂あり）を確認された方がよいと思います。

回答No.1

Cupper-2

MIN関数、MAX関数を使って計算しましょう。

　=MIN("22:00",退社時間)-"17:30"

これで深夜残業をしても残業時間は最大で 22：00 として計算されます。
でもって深夜残業は

　=MAX("22:00".退社時間)-"22:00"

で、計算できます。
退社時間が深夜残業時間より早くても、計算は ０ が返ってくるのでエラー処理をする必要が無くなります。

なお、この計算は休憩時間を考慮していません。
休憩時間を除く場合は、休憩時間前後で上記のような計算を行うようにしてください。
（関数式の意味を理解できれば難しいことではありません。）

※ 時間をダブルクォーテーション（"）で挟むと文字列になりますが、
　　Excel は時間を示す文字列をシリアル値と見なして計算してくれる
　　【お節介機能】を利用しています。
　　このほうが見た目が分かりやすいと思います。