[[20130523141012]]　『各シフト外労働時間（早出、残業）の合計時間』（mking）　ページの最後に飛ぶ

『各シフト外労働時間（早出、残業）の合計時間』（mking）

ExcelMac2011、MacOSXです。

A列から順番に、
A日、B曜日、Cシフト、D勤務開始時間、E勤務終了時間、F労働時間、G当日の合計労働時間、Hシフト外労働時間、I深夜労働時間、J時間外かつ深夜労働時間、の項目を作りました。
K列以降には下記のように、5つのシフトを入力しました。
（シフトAは日勤、シフトBは早番、シフトCは遅番、シフトDは夜勤、シフトEは休み）

 　　　K　　　 L　　　  M
  1　　A 　　9:00　　17:00
  2　　B　 　6:00　　14:00
  3　　C　 14:00　　22:00　　
  4　　D　 22:00　　 7:00(翌朝。31:00と入力)
  5　　E　 　0:00　　 0:00

そして、H列2行目のシフト外労働時間には、
=IF(F2="","",IF(D2<VLOOKUP(C2,K:M,2,FALSE),VLOOKUP(E2,K:M,2,FALSE)-D2,0)+IF(E2>VLOOKUP(C2,K:M,3,FALSE),E2-VLOOKUP(C2,K:M,3,FALSE),0))
と入力しています。

この場合、H列シフト外労働時間が「♯N/A」と表示されてしまうのは、
シフトC（14:00～22:00勤務）のときに11：00から早出した場合や、
シフトD（22:00～翌7:00勤務）のときに12：00から早出した場合で、
早出をしなければきちんと表示されます。

解決方法がわかる方、よろしくお願い致します。

 ＞解決方法がわかる方、よろしくお願い致します

 と云われましても、会社の計算ルールを説明していただかないと、
 どうすればいいのか分からないです。

 項目名から推測するのはやり過ぎの感があります。

 ・・と突き放すのも気が引けるので、たたき台だけ書いておきます。

 H2セル =IF(F2="","",MAX(0,VLOOKUP(C2,K:L,2)-D2)+MAX(0,E2+(E2<D2)-VLOOKUP(C2,K:M,3)))

 (半平太) 2013/05/23(Thu) 21:29

ありがとうございます。
詳しく書きますと、

A列（日にち）、C列（シフト）、D列（勤務開始時間）、E列（勤務終了時間）の4項目入力すれば、他の列が自動表示されるようにしたいです。

F2（その日の労働時間）には、
=IF(COUNT(D2:E2)<>2,"",E2-D2)
と入れています。

G2には、当日の合計労働時間を出したいので（同日に2回出勤した場合のため）
=IF(A2=A3,"",SUMIF(A:A,A2,F:F))
と入れています。

I2（深夜労働時間）には
=IF(COUNT(D2:E2)<2,"",MAX(0,MIN("5:00",E2+(E2<D2))-D2)+MAX(0,MIN("29:00",E2+(E2<D2))-MAX("22:00",D2)))
と入れています。

J2（時間外かつ深夜労働時間）には単純に
=SUM(H2+I2)
と入れました。

H2に教えて頂いたたたき台を入力すると、「♯N/A」の表示は消えましたが、
当日の実際の労働時間以上の時間が表示されてしまう箇所がでてきてしまいました。
どのように変更すれば良いでしょうか？

説明不足等の点がありましたら、指摘下さい。
よろしくお願いします。

 ＞J2（時間外かつ深夜労働時間）には単純に 
 ＞=SUM(H2+I2) 
 と云うことは、時間外とシフト外労働時間は同じ意味なんですか？

 ※会社によっては、（遅刻とかで）既定労働時間(８時間とか）を超えないと
 　時間外扱いしなかったりするんですが、上の解釈でいいのですか？

 ＞当日の実際の労働時間以上の時間が表示されてしまう箇所がでてきてしまいました。
 そうなってしまった、具体的データ(シフト、開始、終了）と正解の時間を教えてください。

 (半平太) 2013/05/24(Fri) 09:56

 ＞＞J2（時間外かつ深夜労働時間）には単純に 
 ＞＞=SUM(H2+I2) 
 ＞と云うことは、「時間外」と「シフト外労働時間」は同じ意味なんですか？

その通りです。
J2は、（シフト外かつ深夜労働時間）、と表記すべきでした。すみません。

＞＞当日の実際の労働時間以上の時間が表示されてしまう箇所がでてきてしまいました。

 ＞そうなってしまった、具体的データ(シフト、開始、終了）と正解の時間を教えてください。

シフトE（休み）、開始6:00、終了10:00　→　正解が4:00（4時間）のところ、10:00（10時間）と表示

シフトE（休み）、開始7:00、終了19:00　→　正解が12:00（12時間）のところ、19:00（19時間）と表示

シフトE（休み）、開始14:00、終了21:00　→　正解が7:00（7時間）のところ、21:00（21時間）と表示

シフトD（22:00～翌7:00）、開始12:00、終了18:00　→　正解が6:00（6時間）のところ、10:00（10時間）と表示

シフトA～Cのところでは、問題ありません。

Dのシフトは、22:00から31:00（翌朝7時）
Eは休みのため、0：00から0：00
とK～M列に入力しているのが原因でしょうか？

（mking）

 ＞Eは休みのため、0：00から0：00 
 Eシフトってそんな意味だったのですかぁー
 ※0:00～0:00は、何かの間違いだと思っていました　(^^ゞ

 ＞シフトD（22:00～翌7:00）、開始12:00、終了18:00
 こりゃ、ぶったまげました。
 シフト時間前に開始して、シフト時間前に終了しちゃう人がいるなんて。
 訊いてみるもんですね。

 こちらに変更してください。
 ↓
 H2セル =IF(F2="","",MAX(0,MIN(VLOOKUP(C2,K:L,2),E2+(E2<D2))-D2)+MAX(0,E2+(E2<D2)-MAX(VLOOKUP(C2,K:M,3),D2)))

 (半平太) 2013/05/24(Fri) 11:16

半平太さん、無事解決いたしました。
説明不足で、お手数おかけました。

大変助かりました！
どうもありがとうございました。

（mking）

コメント返信：

[ 一覧(最新更新順) ]

[[20130523141012]] 『各シフト外労働時間（早出、残業）の合計時間』（mking） ページの最後に飛ぶ

[[20130523141012]]　『各シフト外労働時間（早出、残業）の合計時間』（mking）　ページの最後に飛ぶ