[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『休憩時間と遅刻早退欠勤時間を考慮して実労働時間を算出したい』（若葉）
休憩時間を考慮し、出勤時間、退勤時間、遅刻早退欠勤時間に応じて実労働時間を算出したいと考えています。
休憩時間は以下のとおりです。
休憩１ １０：００〜１０：１５ １５分
休憩２ １２：００〜１３：００ ６０分
休憩３ １５：００〜１５：１５ １５分
Ａ１ 出勤時間
Ｂ１ 退勤時間
Ｃ１ 遅刻・早退・欠勤開始時間
Ｄ１ 遅刻・早退・欠勤終了時間
Ｅ１ 実労働時間
が入力されている状態で、実労働時間を求める関数をご教示ください。
Ａ１ Ｂ１ Ｃ１ Ｄ１ Ｅ１
【通常】 ８：００ １７：００ ７：３０
【早退】 ８：００ １５：３０ １５：３０ １７：００ ６：００
【外出】 ８：００ １７：００ １０：００ １２：００ ５：４５
【遅刻】１３：００ １７：００ ８：００ １２：００ ３：４５
よろしくお願いいたします。
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
早出・残業が無い会社なんですね？
E1セル =IF(B1="","","7:30"-D1+C1+MAX(0,MIN("10:15",D1*1)-MAX("10:00",C1*1))+MAX(0,MIN("13:00",D1*1)-MAX("12:00",C1*1))+MAX(0,MIN("15:15",D1*1)-MAX("15:00",C1*1)))
遅刻して、なおかつ早退した人が居ないといいですけど・・
（半平太） 2017/07/14(金) 19:41
＞早出、残業はありますが、
そう言うことであれば、私の数式は眉唾ものです。 実務で使わないでください。
先ずは全貌をご説明いただく必要があります。
（半平太） 2017/07/15(土) 00:05
実際は以下のようになっています。
Ａ２＝所定労働時間（始業）
Ｂ２＝所定労働時間（終業）
Ａ４＝日付
Ｂ４＝曜日
Ｂ４＝実出勤時間
Ｃ４＝実退勤時間
Ｄ４＝早出残業時間
Ｅ４＝普通残業時間
Ｆ４＝深夜残業時間
Ｇ４＝休日時間
Ｈ４＝休日深夜
Ｉ４＝遅刻・早退・欠勤開始時間
Ｊ４＝遅刻・早退・欠勤終了間
Ｋ４＝実労働時間
Ｌ４＝法定休日
Ａ４ Ｂ４ Ｃ４ Ｄ４ Ｅ４ Ｆ４ Ｇ４ Ｈ４ Ｉ４ Ｊ４ Ｋ４ Ｌ４ Ｍ４
7月1日 土 8：00 15：30 15：30 7：00 6：00
7月2日 日 8：00 0：30 14：00 2：30 12：30 〇
7月3日 月 8：00 18：00 1：00 10：00 12：00 5：45
7月4日 火 7：00 19：00 1：00 2：00 7：30
7月５日 水 13：00 17：30 0：30 8：00 12：00 3：45
7月６日 木 7：00 23：00 1：00 6：00 1：00 7：30
E4に =IF(C4="","",IF($M4="○","",IF(C4<$A$2,$A$2-C4,"")))
F4に =IF(D4="","",IF($M4="○","",IF(D4>$B$2,D4-$B$2,"")))
という式を入れて、満足していましたが、早出が深夜時間にかかっていた場合、または残業が深夜に及んだ場合にまったく対応できないことに先ほど気づきました。
実労働時間算出の関数と併せて、こちらの早出残業、普通残業が深夜にかかった場合にも対応できるような関数をご教示いただきたく存じます。
なお、実労働時間は、基本的には残業時間数も含めたものと考えるの一般的と考えますが、本件の場合には、実労働時間はあくまで休憩時間と遅刻・早退・欠勤時間を考慮し、所定労働時間と比較した場合での時間数が反映されるようにしたいと考えています。
よろしくお願いいたします。
（若葉） 2017/07/15(土) 08:33
＞ 行 ___A___ __B__ __C__ __D__ __E__ __F__ __G__ ____H____ ____I____ _____J_____ _____K_____ __L__ _M_ ＞ 1 始業 終業 ＞ 2 8:00 17:30 ＞ 3 日付 曜 実出 実退 早残 普残 深残 休日時間 休日深夜 遅･退･欠開 遅･退･欠終 実労 法 ＞ 4 7月1日 土 8:00 15:30 15:30 7:00 6:00 ＞ 5 7月2日 日 8:00 0:30 14:00 2:30 12:30 〇 ＞ 6 7月3日 月 8:00 18:00 1:00 10:00 12:00 5:45 ＞ 7 7月4日 火 7:00 19:00 1:00 2:00 7:30 ＞ 8 7月5日 水 13:00 17:30 0:30 8:00 12:00 3:45 ＞ 9 7月6日 木 7:00 23:00 1:00 6:00 1:00 7:30
1. 早退・遅刻のデータ(J,K列)は不要じゃないですか？ ※外出データは中間で居なくなるので必要ですけど。
2. 休日の計算ルールをご説明ください。 7/2のサンプルに加え、その日は外出が10:00〜11:00であった場合でご説明ください。
3. 7/3は実質的に8:00〜10:00、13:00〜18:00、しか拘束されていないので それぞれ、2時間と4時間45分、計6時間45分なので、残業はゼロの筈。 なので普通残業が「0:00」、実労働は「6:45」じゃないですか？
4. 深夜残業時間と深夜勤務時間と同じことになるんでしょうか？ 例えば、22:00出勤で23:00退勤の人(1時間しか働かなかった人）が居た場合、 その人の深夜残業と実労働時間はどうなるんでしょうか？
（半平太） 2017/07/15(土) 13:37
１ おっしゃるとおりJ、Ｋ列は必要ないですが、遅刻早退外出のくくりとして表記したいと考えています。
２ 休日出勤の日は、深夜時間以外は単純に労働時間を積算します。従って、10:00から11:00まで外出した場合はその時間を労働時間から除外します。
３ おっしゃるとおり、実労からいって残業は発生しないことは承知していますが、拘束時間の長さに関わらず所定労働時間以外に労働した場合には残業手当を支給するのが慣例となっておりますので、このようね計算になってしまいます。
４ このようなケースは発生しないと考えていただいて差し支えありません。
ご検討いただきありがとうございます！
（若葉） 2017/07/15(土) 14:48
＞ 3 日付 曜 実出 実退 早残 普残 深残 休日時間 休日深夜 遅･退･欠開 遅･退･欠終 実労 法 ＞ 9 7月6日 木 7:00 23:00 1:00 6:00 1:00 7:30 ↑ ここは何故「5:00」じゃないのですか？
（半平太） 2017/07/15(土) 16:53
１．レイアウトを若干変えます。→ データは5行目からとします。
２．数式およびサンプルデータの転記は面倒なので、以下のマクロを実行して埋めてちゃって下さい。
以下は、新規ブックでやってください。
新規シートの「シート見出し」を右クリックして、「コードの表示(V)」を選ぶと 画面中央に白いエリアが表れます。(VBE画面です）
そのエリアに後記マクロをコピぺし、F5キーを押下してください (すると、マクロ「onlyOnce」が実行され、自動的にデータと数式が入力されます）
※実行は１回だけですので、終わったらマクロコードは消去し、ALT + F11キー でエクセルに戻ってください。
’貼り付けるマクロコード
’ ↓
Private Sub onlyOnce()
Rem 結合状態を処理
Range("Q1:U1").Merge
Range("X1:AB1").Merge
Rem 生データのセルをまとめて処理
Range("A1").Value = "始業"
Range("B1").Value = "午前休"
Range("C1").Value = "昼休"
Range("D1").Value = "午後休"
Range("E1").Value = "早朝深夜"
Range("F1").Value = "夜更深夜"
Range("Q1").Value = "拘束時間"
Range("X1").Value = "外出時間"
Range("A2,C5:C7,J9").Value = 0.333333333333333
Range("B2,J7").Value = 0.416666666666667
Range("C2,K7,K9").Value = 0.5
Range("D2").Value = 0.625
Range("E2").Value = 0
Range("F2").Value = 0.916666666666667
Range("O2").Value = "NET"
Range("P2").Value = "実(含外出)"
Range("Q2,X2").Value = "休憩1"
Range("R2,Y2").Value = "休憩2"
Range("S2,Z2").Value = "休憩3"
Range("T2,AA2").Value = "深夜1"
Range("U2,AB2").Value = "深夜2"
Range("W2").Value = "外"
Range("A3").Value = 0.708333333333333
Range("B3").Value = 0.427083333333333
Range("C3,C9").Value = 0.541666666666667
Range("D3").Value = 0.635416666666667
Range("E3").Value = 0.208333333333333
Range("F3").Value = 1.20833333333333
Range("A4").Value = "日付"
Range("B4").Value = "曜日"
Range("C4").Value = "実出勤時間"
Range("D4").Value = "実退勤時間"
Range("E4").Value = "早出残業時間"
Range("F4").Value = "普通残業時間"
Range("G4").Value = "深夜残業時間"
Range("H4").Value = "休日時間"
Range("I4").Value = "休日深夜"
Range("J4").Value = "遅退欠始"
Range("K4").Value = "遅退欠終"
Range("L4").Value = "実労働時間"
Range("M4").Value = "法定休日"
Range("A5").Value = 42917
Range("B5").Value = "土"
Range("D5,J5").Value = 0.645833333333333
Range("K5,C8,C10").Value = 0.291666666666667
Range("A6").Value = 42918
Range("B6").Value = "日"
Range("D6").Value = 2.08333333333333E-02
Range("M6").Value = "○"
Range("A7").Value = 42919
Range("B7").Value = "月"
Range("D7").Value = 0.75
Range("A8").Value = 42920
Range("B8").Value = "火"
Range("D8").Value = 0.791666666666667
Range("A9").Value = 42921
Range("B9").Value = "水"
Range("D9").Value = 0.729166666666667
Range("A10").Value = 42922
Range("B10").Value = "木"
Range("D10").Value = 0.958333333333333
Rem 数式セルをまとめて処理
Range("E5:E10").FormulaR1C1Local = "=IF(RC[8]="""",MAX(0,MIN(RC4+(RC4<RC3),""8:00"")-MAX(RC3,""5:00"")),"""")"
Range("F5:F10").FormulaR1C1Local = "=IF(RC[8]="""",MAX(0,MIN(RC4+(RC4<RC3),""22:00"")-MAX(RC3,""17:00"")),"""")"
Range("G5:G10").FormulaR1C1Local = "=IF(RC[6]="""",SUM(RC[13]:RC[14])-SUM(RC[20]:RC[21]),"""")"
Range("H5:H10").FormulaR1C1Local = "=IF(RC[5]="""","""",RC[-4]-RC[-5]+(RC[-4]<RC[-5])-RC[1]-(RC[3]-RC[2]+(RC[3]<RC[2])))"
Range("I5:I10").FormulaR1C1Local = "=IF(RC[4]<>"""",SUM(RC[11]:RC[12])-SUM(RC[18]:RC[19]),"""")"
Range("L5:L10").FormulaR1C1Local = "=IF(RC[1]="""",RC[3]-SUM(RC[-7]:RC[-5]),RC[3]-RC[-3])"
Range("O5:O10").FormulaR1C1Local = "=RC[1]-RC[8]"
Range("P5:P10").FormulaR1C1Local = "=RC[-12]-RC[-13]+(RC[-12]<RC[-13])-SUM(RC[1]:RC[3])"
Range("Q5:U10").FormulaR1C1Local = "=MAX(0,MIN(RC4+(RC4<RC3),R3C[-15])-MAX(RC3,R2C[-15]))"
Range("W5:W10").FormulaR1C1Local = "=IF(AND(COUNT(RC[-13]:RC[-12])=2,OR(RC[-19]+(RC[-19]<RC[-20])<=RC[-13],RC[-12]+(RC[-12]<RC[-13])<=RC[-20])),0,RC[-12]-RC[-13]+(RC[-12]<RC[-13])-SUM(RC[1]:RC[3]))"
Range("X5:Z10").FormulaR1C1Local = "=MAX(0,MIN(RC11+(RC11<RC10),R3C[-22])-MAX(RC10,R2C[-22]))"
Range("AA5:AA10").FormulaR1C1Local = "=IF(AND(COUNT(RC[-17]:RC[-16])=2,OR(RC[-23]+(RC[-23]<RC[-24])<=RC[-17],RC[-16]+(RC[-16]<RC[-17])<=RC[-24])),0,MAX(0,MIN(RC11+(RC11<RC10),R3C[-22])-MAX(RC10,R2C[-22])))"
Range("AB5:AB10").FormulaR1C1Local = "=IF(AND(COUNT(RC[-18]:RC[-17])=2,OR(RC[-24]+(RC[-24]<RC[-25])<=RC[-18],RC[-17]+(RC[-17]<RC[-18])<=RC[-25])),0,MAX(0,MIN(RC11+(RC11<RC10),R3C[-22])-MAX(RC10,R2C[-22])))"
Rem 標準外書式セルをまとめて処理
Range("A1,C5:D10").NumberFormatLocal = "h:mm"
Range("A2:F2,A3:L3").NumberFormatLocal = "[h]:mm;@"
Range("A5:A10").NumberFormatLocal = "m""月""d""日"""
Range("E5:AB10").NumberFormatLocal = "[$-409][h]:mm;-G/標準;;@"
Rem 塗りつぶしセルをまとめて処理
Range("E5:I10,L5:L10,O5:U10,W5:AB10").Interior.ColorIndex = 40
End Sub
（半平太） 2017/07/15(土) 19:21
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.