[[20210406231507]]　『早朝、深夜残業の計算式』（Emma）　ページの最後に飛ぶ

『早朝、深夜残業の計算式』（Emma）

D9に始業時間
E9に就業時間が入っています。

8:30-17:30が通常勤務時間です。
8:30前と17:30以降が残業時間です。
残業時間を求めたいです。

下記のように記述しましたがうまくいきません。
=IF(E9<=TIME(0,0,0),E9-TIME(17,30,0)+TIME(8,30,0)-D9,IF(AND(E9>=TIME(0,0,0),E9<=TIME(8,30,0)),E9+"24:00"-TIME(17,30,0)+TIME(8,30,0)-D9,""))

どのように設定すればよろしいでしょうか。

＜使用 Excel：unknown、使用 OS：unknown ＞

ちょっと確認。
遅刻や早退、時間休暇を一切考えないなら

  _____D___________E_________F________G_________H_______
 8  始業時間   終業時間   定時前   定時後   残業時間
 9    8:00      18:00      0:30     0:30      1:00

 F9セルに「="8:30"-D9」
 G9セルに「=E9-"17:30"」

みたいにすればよいから、

 H9 ="8:30"-D9+E9-"17:30"

みたくなるとおもうんですが、何故IF関数で分岐させてるんですか？

（もこな２） 2021/04/07(水) 07:25

 タイトル
 ＞早朝、深夜残業の計算式

 本文
 ＞8:30前と17:30以降が残業時間です。
 ＞残業時間を求めたいです。

 タイトルは「、深夜残業」となっていますが、本文は単に「残業時間」となっています。
 本当はどっちなんですか？

 また、「通常時間外の時間」と「残業時間」は必ずしもイコールじゃないですよね？
 例えば、始業13:00、終業19:00だったら、残業は0:00と思うんですが、そうじゃないんですか？

（半平太） 2021/04/07(水) 09:24

  _____D___________E_________F________G________
  始業時間   終業時間   残業時間  深夜残業   
     8:30      17:30      0:00     0:00   
     8:30      18:30      1:00     0:00  
     8:30      24:30      4:30     2:30   
     7:30      22:30      5:30     0:30 
     4:30      17:30      3:30     0:30

定時8:30-17:30
残業　5:00-8:30、17:30-22:00　→　1時間2500円
深夜　5:00前、22:00-5:00　→　1時間3000円

うまく条件分岐できません。

（Emma） 2021/04/07(水) 21:51

すみません、残業時間と深夜残業の時間の求め方を教えてください。

（Emma） 2021/04/07(水) 21:53

 残業について補足説明がないので適当にやりますよ。

 F2セル =MAX(0,E2+(E2<D2)-D2-MAX(0,MIN("17:30",E2+(E2<D2))-MAX("8:30",D2))-G2)
 G2セル =MAX(0,MIN("5:00",E2+(E2<D2))-D2)+MAX(0,MIN("29:00",E2+(E2<D2))-MAX("22:00",D2))

 <結果図>
 行  ____D____  ____E____  ____F____  ____G____
  1  始業時間   終業時間   残業時間   深夜残業 
  2     8:30      17:30       0:00       0:00
  3     8:30      18:30       1:00       0:00
  4     8:30      24:30       4:30       2:30
  5     7:30      22:30       5:30       0:30
  6     4:30      17:30       3:30       0:30

（半平太） 2021/04/07(水) 23:21

自身が最初に書いてる関数がなんでできないのかわからないってこと？

=IF(E9<=TIME(0,0,0),E9-TIME(17,30,0)+TIME(8,30,0)-D9,IF(AND(E9>=TIME(0,0,0),E9<=TIME(8,30,0)),E9+"24:00"-TIME(17,30,0)+TIME(8,30,0)-D9,""))

これを分解すると
1.E9>="0:00"の場合
2.E9<"0:00"かつ"0:00"<=E9<="8:30"の場合
3.1でも2でもない（この場合は空欄になるようになってるね）
の3パターンに分かれるんだけど2は明らかに矛盾してるから1じゃなかった時点で自動的に3の空欄になる
1の場合は
E9-"17:30"+"8:30"-D9
だから多分あってるけど
2の場合は（絶対FALSEになるって話はおいておいて）
E9+"24:00"-"17:30"+"8:30"-D9
ってもうよくわかんないよね
なんで24時間足しちゃってるの
想定としては日付が変わってから帰った場合みたいなところなんだとは思うけど深夜残業って22時からだよね
"0:00"<=E9<="8:30"が条件じゃ22時から24時退社が抜けてるよね
求め方以前に自分がどういう関数を書いてるか理解してないですよね
（あさ） 2021/04/07(水) 23:35

＞1.E9>="0:00"の場合
この部分見間違えてました申し訳ないです。

1.E9<="0:00"の場合
2."0:00"<E9かつ"0:00"<=E9<="8:30"の場合
3.1でも2でもない場合
で2は矛盾してないですね。
代わりに1の場合が実質0時退社しかTRUEにならないのでおかしなことになってますが

（あさ） 2021/04/07(水) 23:50

半平太さん、あささん、
ご教示いただきありがとうございます。
今夜リファレンスをみて、教えていただいた関数を分解してみます。
自分でもきちんと理解できないまま、公式に詰め込んでしまいました。
本当にすみません。

あと始業:13:00、終業:19:00でも、残業は、19:00-17:30の1:30になるのです。
なので、通常の時間外は、残業時間なのです。
特殊なのですが。

また不明点がでてきましたら、
質問させて下さい。

（Emma） 2021/04/08(木) 07:52

 ＞あと始業:13:00、終業:19:00でも、残業は、19:00-17:30の1:30になるのです。
 ＞なので、通常の時間外は、残業時間なのです。

 なら、私の提示した式でOKです。

 こう言う計算は、ほぼ定番化しているものがあるのでそれを使うと楽です。

 ある時間帯(aからb)と別の時間帯(cからd)のオーバーラップしている時間の計算方法
 = Max(0,Min(b,d)-Max(a,c))

  何故最初にMax(0,・・）とするかと言うと、
  オーバーラップがないとマイナスになるので、それを0に換算するため。

  ※小数演算誤差で、本来なら0なのだが限りなく0に近いマイナス値もこの効果で補正される。
 　 しかし、本来なら0なのだが限りなく0に近い「プラス」値は補正されない。 
    このプラス誤差は実務的には無害だが、0を空白表示にする書式を設定しても 0:00 と表示される(完全には0じゃないので）

（半平太） 2021/04/08(木) 09:17

残業時間の合計値について質問させてください。

SUMで残業時間を計算してるのですが、
フラグが立っているセルは、空欄を埋めています。""を入れています。
そうすると合計時間がおかしくなってしまいます。
セルの設定は、[h]:mm;;です。
合計値を正しく表示する方法を教えて頂けませんでしょうか。
（Emma） 2021/04/12(月) 01:48

すみませんでした。できました。
また分からないことがありましたら、よろしくお願いいたします。
（Emma） 2021/04/12(月) 05:57

コメント返信：

[ 一覧(最新更新順) ]

[[20210406231507]] 『早朝、深夜残業の計算式』（Emma） ページの最後に飛ぶ

[[20210406231507]]　『早朝、深夜残業の計算式』（Emma）　ページの最後に飛ぶ