深夜勤務ありのタイムシート

質問

タイムシートに出勤・退社時間をいれると自動的に勤務時間（実働合計）、法定内時間（am5:00～pm10:00)、深夜時間（pm10:00～am5:00) 法定外時間（勤務時間が8時間越えた時間）が計算されるシートを作り
たいと思っています。

（例）
1.出勤時間16：00　退社0：00の場合
　実働時間　8ｈ　法定内時間　6ｈ　深夜時間　2ｈ

2.出勤時間23：00　　退社9：00の場合（休憩1ｈ）
　実働時間　9ｈ　法定内時間　3ｈ　深夜時間　5ｈ　法定外時間　1ｈ

というようなタイムシートです。

A　　　　 B　　　C　　 D 　E F
出勤 　 退勤 実働　法定内 法定外 深夜
16：00　0：00　8:00　6:00 0:00 2：00
23：00　9：00　9:00　3:00 1:00 5：00

このような場合計算式はどのように入れればいいのでしょうか？
22：00以降は深夜勤務になるという式は検索して分かったのですが
am5:00を超えると法廷内勤務になるとか
分からなくなってきました。長文で説明下手で
申し訳ないですが宜しくお願いします。

回答No.1

guchi_yama

こんにちは。

質問者のような勤務体系の経験がないので、
何時に出社すれば何時から休憩が取れるのか？
深夜に伴う残業も昼勤の残業と同様の法定外時間になるのか？
(法定外2.5割増＋深夜2.5割増の計5割増でないのか？）等々、
疑問もたくさんある中で、
勤務日報に伴う時間の出し方は、定義すべき問題があり過ぎて、
非常に難しいと思います。
私自身も個人日報に関数を入れて作っていますが、
休日のときや出勤時間がお昼からのときや遅刻をしたときなどで、
それぞれ答えが変わる為、
IF()をネストさせ繋げて自分なりに駆使して作っています。
が、
有休、代休を使ったときや、徹夜後に続けて勤務したときなど、
特殊な日はどうしても手入力が必要となります。

とりあえず、質問者の定義より、
実働時間に入る式を考えてみましたので、
参考にしてみて下さい。

C1に、
=IF(AND(A1>=TIMEVALUE("5:00"),A1<=TIMEVALUE("13:00"),(B1-A1)>=TIMEVALUE("9:00")),TIMEVALUE("8:00"),IF(AND(A1>=TIMEVALUE("5:00"),A1<=TIMEVALUE("13:00"),(B1-A1)<TIMEVALUE("9:00")),(B1-A1)-TIMEVALUE("1:00"),IF(AND(A1>TIMEVALUE("13:00"),A1<=TIMEVALUE("21:00"),B1<=TIMEVALUE("22:00")),(B1-A1)-TIMEVALUE("1:00"),IF(AND(A1>TIMEVALUE("13:00"),A1<=TIMEVALUE("21:00"),B1<=(TIMEVALUE("23:59")+TIMEVALUE("5:01"))),(TIMEVALUE("22:00")-A1),IF(AND(A1>TIMEVALUE("13:00"),A1<=TIMEVALUE("21:00"),B1>(TIMEVALUE("23:59")+TIMEVALUE("5:01"))),(TIMEVALUE("22:00")-A1)+(B1-(TIMEVALUE("23:59")+TIMEVALUE("5:01"))),IF(AND(A1>=TIMEVALUE("22:00"),A1<(TIMEVALUE("23:59")+TIMEVALUE("5:01"))),B1-TIMEVALUE("5:00"),""))))))

※注　ただ、深夜に関しての休憩時間を
どのタイミングで引けばいいのか分からなかったので、
深夜主体の勤務時間に関しては休憩時間を法定内時間からは引いていません。
また、24：00を超える時間の入力に関しては、
当該時間に24：00をプラスして下さい。
例）午前4時の場合　→　28：00

法定内だけでこれだけの関数が必要となります。
また、
上記関数でも8時から勤務の人が遅刻して昼から勤務をした場合、
13時～17時の4時間勤務となるが、上記式では13時を入力した時点で
昼からの業務と見なし、通常通り1時間の休憩を引いた答え(3時間)を
返すようにしている為、その辺の改善も必要となる。
よって、質問者の会社の勤務体系に見合ったものを作るとなると、
細かい情報まで加味していかないと意味の無いデータとなるでしょうし、
そこまでの細かいデータを取り入れていくとなると、
かなりのデータ量になると思われます。