159

view

何点か質問しましたが、回答がいただけないようなので、推測で再回答します。

やはり少し見にくいですが、下の図のようなことでよろしいでしょうか？

①A10: 日付=IF(ROW()=10,DATE(A$6,C$6,1),IF(A9="","",IF(MONTH(A9)<>MONTH(A9+1),"",A9+1)))
②B10: 曜日=IF(A10="","",TEXT(A10,"aaa"))
③F10: 日数=IF(COUNT(D10:E10)<2,"",IF(IF(AND(E10<=D10,E10<="7:00"*1),E10+1,E10)-D10<"4:00"*1,0.5,1))
④G10: 欠勤=IF(OR(C10={"欠勤","生理休暇"}),1,"")
⑤H10: 所定時間内勤務時間=IF(COUNT(D10:E10)<2,"",IF(C10="休日出勤",0,MIN(O$2,IF(AND(E10<=D10,E10<="7:00"*1),E10+1,E10))-MAX(M$2,D10)-I10))
⑥I10: 休憩時間=IF(COUNT(D10:E10)<2,"",IF(C10="半日有給",0,MIN(MAX(IF(AND(E10<=D10,E10<="7:00"*1),E10+1,E10),"13:00"),"14:00")-MIN(MAX(D10,"13:00"),"14:00")))
⑦J10: 法定内残業時間=IF(COUNT(D10:E10)<2,"",IF(C10="休日出勤",0,IF(AND(OR(D10<M$2,IF(AND(E10<=D10,E10<="7:00"*1),E10+1,E10)>O$2),O$2-M$2-M$6<"8:00"*1),MIN((M$2-MIN(D10,M$2))+(MIN(IF(AND(E10<=D10,E10<="7:00"*1),E10+1,E10),"22:00")-O$2),"8:00"*1-(O$2-M$2-M$6)),0)))
⑧K10: 所定外残業時間=IF(COUNT(D10:E10)<2,"",IF(C10="休日出勤",0,IF(COUNT(D10:E10)<2,0,MAX(0,MAX(0,M$2-D10)+(MIN(IF(AND(E10<=D10,E10<="7:00"*1),E10+1,E10),"22:00"*1)-O$2-J10)))))
⑨L10: 法定休日時間=IF(COUNT(D10:E10)<2,"",IF(C10<>"休日出勤",0,IF(AND(E10<=D10,E10<="7:00"*1),E10+1,E10)-D10-I10))
⑩M10: 深夜勤務時間=IF(COUNT(D10:E10)<2,"",IF(C10="休日出勤",0,MIN(MAX(IF(AND(E10<=D10,E10<="7:00"*1),E10+1,E10),"22:00"),"31:00")-MIN(MAX(D10,"22:00"),"31:00")))
⑪N10: 控除時間=IF(COUNT(D10:E10)<2,"",MAX(TIMEVALUE("8:00")-(IF(AND(E10<=D10,E10<="7:00"*1),E10+1,E10)-D10-I10),0))
⑫O10: 総労働時間=IF(COUNT(D10:E10)<2,"",H10+J10+K10+L10+M10)

1）それぞれの式が長くなっていますが、E列の終業時刻が深夜の2時などになったときに「2:00」などと入力してもエラーにならないように「E10」を「IF(AND(E10<=D10,E10<="7:00"*1),E10+1,E10)」としています。
2）③F10の日数は、文章から判断して少し式を変えています。
3）⑪N10の控除時間は、8時間から、残業も含めた総勤務時間を引くのか、所定時間内勤務時間だけを引くのか、が分からなかったのでそのままの数式にしています。
4）⑫O10の総労働時間の数式については、控除時間を引くとおかしくなるように思いましたので、その部分を削除しました。

以上で、M2セルの基本出勤時刻とO2セルの基本退勤時刻とに通常勤務と時短勤務の2パターンを入れ替えてみて、D10の出勤時刻とE10の退勤時刻とにいろいろと時刻を入れて、試してみて下さい。
貴殿の意図されるところと異なるのであれば、具体的に補足して下さい。

なお、下の画像の基になったエクセル表をヤフーボックスにアップしましたので、許されるならダウンロードしてみて下さい。もちろんウイルスのようなものは入っていません（作れません）。
想定し得るいろんな時刻を入れて検証してみました。

https://box.yahoo.co.jp/guest/viewer?sid=box-l-wteps5mtoszopeb7a6kqxwcrei-1001&uniqid=83c4c3d1-5ed0-4c58-8a94-02c9d15dfcdd&viewtype=detail

少し教えて下さい。
通常勤務で、
ア）基本勤務時刻が9:00～18:00の人が、9:00～19:30まで勤務した場合の、①所定内勤務時間と、②法定内残業時間と、③所定外残業時間、
イ）時短勤務者で基本勤務時刻が9:30～17:30の人が、9:30～19:30まで勤務した場合の、④所定内勤務時間と、⑤法定内残業時間と、⑥所定外残業時間、

のそれぞれ①～⑥の時間数を教えて下さい。

最初のの質問に答えようと思ったら締め切られてしまったので、それを元に回答します。

最初のの質問文中の式では、2つ目のIF文の条件は所定勤務時間が8時間未満、かつ実勤務時間が所定勤務時間を超えた場合となっていると思います。
これに条件が一致した時に、8時間から実勤務時間を引いたものを残業時間としていますが、ここがおかしいのではないでしょうか？

求める残業時間は実勤務時間から所定勤務時間を引いたものになります。

さらに実勤務時間が8時間を超えた場合の分岐も必要です。
8時間以上勤務を行った場合には、法定内残業時間は8時間と所定勤務時間の差になります。

以上をまとめると↓の式になります。
=IF(OR(D12=0,E12=0),"",IF(AND(($O$2-$M$2-$M$6)<TIMEVALUE("8:00"),(E12-D12-I12)>($O$2-$M$2-$M$6)),IF((E12-D12-I12)<TIMEVALUE("8:00"),(E12-D12-I12)-($O$2-$M$2-$M$6),TIMEVALUE("8:00")-($O$2-$M$2-$M$6)),"0:00"))

おそらく大丈夫だと思いますが、不具合あれば補足でおかしい部分を質問ください。