[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『時間外労働と休憩の時間を関数で計算したい』（げん）
いつもお世話になっています。
時間外労働と休憩時間について関数でまとめたいと思っています。
A列に出勤時刻、B列に退勤時刻、C列に残業時間を手入力します。
ここで
・出退勤時刻に9時間差以上ある場合は残業時間を算出する（8時間労働+1時間休憩）
・残業時間は30分単位で端数切り下げ（切り上げる場合は手入力で退勤時間を遅くする）
・22〜5時の時間外労働時間とそれ以外の時間外労働時間を分ける（深夜割増手当の関係）
・残業時間中の中抜け時間は労働時間から抜く（30分単位での中抜けが可能）
・中抜け時間は時間外労働の休憩→深夜時間外労働の休憩の順で自動計算（出退勤時刻と残業時間だけでは計算できないため）
※出退勤時刻差と残業時間の差分で中抜け時間を計算して、普通時間外労働→深夜時間外労働の順に減らすという要領です。
これらを関数で出したいと思っています。
今まではマクロで計算値を自動入力していましたが、A〜C列を後から入力することがあり計算に間違いが生じていたため
関数を入れてA〜C列の後入力にも柔軟に対応したいと思っています。。
今のところ、D列（D2セル）には普通時間外時間（労働＋休憩）として
=IF(IF(A2>=B2,1,)+B2-A2<TIME(9,,),,ROUND((IF(A2>=B2,1,)+B2-A2-TIME(1,,))*48,0)*0.5-8-E2)
E列（E2セル）には深夜時間外時間（労働＋休憩）として
=ROUND(MIN(SUM(IF(A2<=TIME(5,,),TIME(5,,)-A2,),IF(A2>=B2,IF(B2>=TIME(5,,),TIME(5,,),B2),),IF(B2>TIME(22,,),B2-TIME(22,,),),IF(A2>B2,IF(A2<=TIME(22,,),TIME(2,,),1-A2),)),MAX(IF(A2>B2,1,)+B2-A2-IF(IF(A2>B2,1,)+B2-A2>=TIME(9,,),TIME(1,,),)-TIME(8,,),))*48,0)*0.5
F列（F2セル）には普通時間外労働時間として
=IF(C2>SUM(D2:E2),"ERROR",IF(C2>E2,C2-E2,0))
G列（G2セル）には深夜時間外労働時間として
=IF(C2>SUM(D2:E2),"ERROR",IF(C2>E2,E2,C2))
H列（H2セル）には普通時間外休憩時間として
=IF(F2="ERROR","ERROR",D2-F2)
I列（I2セル）には深夜時間外休憩時間として
=IF(G2="ERROR","ERROR",E2-G2)
という数式が入っています。
ここからが本題ですが、
・時間計算のエラーを無くしたい
時間計算をすると小数点部分で生じる誤差をなくすためにD列にROUND関数を使用していますが、
現状では15〜29分を切り上げて計算してしまっている。
切り下げにすると別のセルで計算上の問題が生じる（出退勤時間差9時間で入力しても8時間半で計算されてしまう等）ので
これを解消したい
・時間外休憩時間の調節がしたい
現状では時間外の休憩時間を、普通時間外労働から順に差し引くようになっているが
実際には深夜に中抜け時間があったりなどしているので実情に対応させたい
・式が長すぎる＆使用する列数が多すぎるのでもっと短く＆少なくしたい
・そもそもこの式で上記の内容を満たしているかが不安
何か間違いの生じる数式になっていたら指摘していただけるとありがたいです
以上お手数ですがよろしくお願いします。
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
・時間計算のエラーを無くしたい 時間計算をすると小数点部分で生じる誤差をなくすためにD列にROUND関数を使用していますが、 現状では15〜29分を切り上げて計算してしまっている。 切り下げにすると別のセルで計算上の問題が生じる（出退勤時間差9時間で入力しても8時間半で計算されてしまう等）ので これを解消したい
この点だけ。
=trunc(...+time(,,1))
とすることで改善できるはずです。
（sheet無限増殖） 2019/05/09(木) 14:39
＞・時間外休憩時間の調節がしたい ＞ 現状では時間外の休憩時間を、普通時間外労働から順に差し引くようになっているが ＞ 実際には深夜に中抜け時間があったりなどしているので実情に対応させたい
それを実現するのに必要な情報を、どんな形で追加するお積りなんですか？ その追加データも加味して、希望結果図(サンプル図）をアップしてください。
３、４例は必要。 ※ 典型的なケースは１つにして、あとは出来るだけ複雑なケースを書いてください。
（半平太） 2019/05/09(木) 19:19
昨日の時点でD列、E列の数式を以下のように修正しました。
D列（D2セル）
=IF(IF(A2>=B2,1,)+B2-A2<TIME(9,,),,TRUNC(((IF(A2>=B2,1,)+B2-A2-TIME(1,,))+TIME(,,1))*48,0)*0.5-8-E2)
※ROUND(,0)をTRUNC(+time(,,1))に変更しました
E列（E2セル）
=TRUNC((MIN(SUM(IF(A2<=TIME(5,,),TIME(5,,)-A2,),IF(A2>=B2,IF(B2>=TIME(5,,),TIME(5,,),B2),),IF(B2>TIME(22,,),B2-TIME(22,,),),IF(A2>=B2,IF(A2<=TIME(22,,),TIME(2,,),1-A2),)),IF(IF(A2>B2,1,)+B2-A2>=TIME(9,,),IF(A2>B2,1,)+B2-A2-TIME(9,,),))+TIME(,,1))*48)*0.5
※ROUND(,0)をTRUNC(+time(,,1))に変更しました
※途中のA2>B2を一か所A2>=B2に変更しました
※MAX(IF(A2>B2,1,)+B2-A2-IF(IF(A2>B2,1,)+B2-A2>=TIME(9,,),TIME(1,,),)-TIME(8,,),)を
IF(IF(A2>B2,1,)+B2-A2>=TIME(9,,),IF(A2>B2,1,)+B2-A2-TIME(9,,),)に変更しました
そのうえで半平太さんのご指摘についてですが
・理想としては、H列にだけ値を上書きして修正したいです。
I列に値を打ち込む→それに応じてF,G,H列の値が変化
というものを目指しています。
言い換えると
（もともと深夜休憩時間(I)が最小になるように式がつくられているので）
I列の値を増やすことで普通時間外の休憩時間(H)が減る、
（I列の値が変化するので）G列の値が変化する
（H列の値が変化するので）F列の値が変化する
というかたちにしたいと思っています。
シートはテンプレートにして各月分をコピーして使用しますので
原則としてI列に数式を復活させることはありません。
（元の数式を復活させるマクロはつくるかもしれません）
これからサンプル図も作成し書き込みたいと思います。
引き続きよろしくお願いします。
（げん） 2019/05/10(金) 09:48
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |<実働時間>
[1]|出勤時刻|退勤時刻|残業時間|普通残業時間|深夜残業時間|普通残業休憩|深夜残業休憩|
[2]|8:55 |18:04 | | 0| 0| 0| 0|9:00〜12:00、13:00〜18:00
[3]|8:45 |18:16 | 1| 1| 0| 0.5| 0|8:00〜12:00、13:00〜18:00
[4]|4:20 |22:45 | 6| 5| 1| 3| 0|4:30〜12:00、13:00〜18:00、21:00〜22:30
[5]|12:57 |0:31 | 2.5| 0| 2.5| 0| 0|13:00〜18:00、19:00〜0:30
[6]|7:55 |23:01 | 2| 1| 1| 3| 0|8:00〜12:00、16:00〜23:00
[7]|21:54 |7:06 | 1| 0| 1| 0| 0|22:00〜7:00
[8]|8:49 |3:16 | 4| 2| 2| 2| 3|9:00〜12：00、13：00〜20:00、1:00〜3:00
[9]|23:30 |14:30 | 4| 0| 4| 0.5| 1.5|23:30〜3:00、4:00〜8:00、9:00〜12:00、13:00〜14:30
標準は9:00始業、18:00終業です。
原則9時間勤務で1時間の休憩があります。
ただししばしば変則勤務形態になる社員がいるため
さまざまな勤務形態に対応した勤務表にしなくてはいけなくなりました。
[A][B][C]列は手入力、[D][E][F][G]列には数式が記入されています。
ルールはいくつかあります。
(1)標準勤務時間は8時間+休憩時間1時間（この休憩時間を表す列は作らない）
(2)8時間を超える勤務時間は残業時間として計上
(3)残業時間は0.5時間（30分）単位で計上、端数時間は切り捨て（切り上げる場合は打ち込む時点で30分単位に切り上げて記入）
(4)出勤前後の端数時間合計が30分を超えていても切り捨てる場合がある（残業時間を打ち替えることで対応）
(5)残業時間のうち、22:00〜5:00の分は深夜残業時間として計上（深夜分が30分未満の場合は普通の時間外に算入）
(6)(5)の時間に勤務していた場合の時間外勤務は、深夜残業時間から先に計上する（[7]の例参照）
(7)(1)の休憩時間を取得せず勤務した場合は時間外として計上（残業時間に記入）
現状[8]の例では深夜残業時間から先に計上するので
[8]|8:49 |3:16 | 4| 0| 4| 4| 1| となってしまいます。こういうケースに関数だけで対応できればありがたいのですが、無理な場合は D〜G列のいずれか（できればG列だけ）を手打ちで打ち替えることで他3列の値が再計算されるようにできたらありがたいです。 （同じ表は再利用しません。）
どうしても関数のみでは難しい場合は、[A]〜[C]列のいずれかを打ち替えた場合に[D]〜[G]列が自動で打ち替わったり
[G]列を打ち替えた場合に[D]〜[F]列が自動で打ち替わるようなマクロも検討したいと思っています。
サンプル、ルールの不足やこちらの見当違いなどありましたらご指摘いただけるとありがたいです。
お手数で申し訳ありませんがご検討よろしくお願いします。
（げん） 2019/05/14(火) 11:49
＞ |[A] |[B] |[C] |[D] |[E] |[F] |[G] |<実働時間>
＞ [1]|出勤時刻|退勤時刻|残業時間|普通残業時間|深夜残業時間|普通残業休憩|深夜残業休憩|
＞ [3]|8:45 |18:16 | 1| 1| 0| 0.5| 0|8:00〜12:00、13:00〜18:00
↑
ここが0.5になる根拠は何ですか？
＞(4)出勤前後の端数時間合計が30分を超えていても切り捨てる場合がある（残業時間を打ち替えることで対応）
この部分は、さっぱり分かりません。どんな条件が揃うと、切り捨てになるんですか？
（半平太） 2019/05/14(火) 16:51
(4)については、関数またはマクロで及ばないところがあるという例示として記載しました。
このような相談をしている原因の大きな一つで、[B]-[A]の単純計算では残業時間が発生していても
管理職側の判断で算入されないというケースがあるということです。
まさに前半部分と同様の例で、当初1.5と書かれていたものがあとから1に書き換えられることがあるのです。
余談になりますが、従前使われていたマクロはこういうケースで1.5をもとに算出して値を直接記入していたために1に書き換えられても変更を反映してくれないという問題が発生していました。
（[8]のようなケースを修正したものを「元に戻してしまう」ので、再計算もできませんでした。）
（げん） 2019/05/14(火) 17:08
ロジカルな処理と勘違いしておりました。
かなりアナログな処理が絡んでいるようで、 私にはお役に立てそうもないです。
すみませんが、他の回答者のレスをお待ちください。 m(__)m
（半平太） 2019/05/14(火) 19:07
カンバックしました。 m(__)m
よくよく読んだら、当初の質問と同じだった (-_-;)
以下２つ、疑問点です。
＞ [1]|出勤時刻|退勤時刻|残業時間|普通残業時間|深夜残業時間|普通残業休憩|深夜残業休憩| ＞ [6]|7:55 |23:01 | 2| 1| 1| 3| 0|8:00〜12:00、16:00〜23:00
上のケースでは9時間超の部分が6時間で、残業が2時間ですから、休憩は「4」時間だと思われるのですが、 何故「3」時間なのでしょうか？
＞ [1]|出勤時刻|退勤時刻|残業時間|普通残業時間|深夜残業時間|普通残業休憩|深夜残業休憩| ＞ [7]|21:54 |7:06 | 1| 0| 1| 0| 0|22:00〜7:00
上のケースでは拘束時間が9時間なので、残業は発生しないと思うのですが、 残業1時間となっています。これはどう考えればいいんでしょうか？
（半平太） 2019/05/15(水) 22:34
２つ目のは、これが当てはまるんでしょうか？ ↓ ＞(4)出勤前後の端数時間合計が30分を超えていても切り捨てる場合がある（残業時間を打ち替えることで対応）
そうだとして、もし、切り捨てる端数が無かったらどうするんでしょうか？
こんな場合。(こんなデータはあり得ないと考えていいんですか？） ↓ ＞ [1]|出勤時刻|退勤時刻|残業時間|普通残業時間|深夜残業時間|普通残業休憩|深夜残業休憩| ＞ [7]|22:00 |7:06 | 1| 0| 1| 0| 0|22:00〜7:00
（半平太） 2019/05/16(木) 00:19
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.