エクセル出退勤表で時間外と深夜時間外の関数
エクセルで出退勤表を作りたいと思っています。
所定労働時間は10:00~18:30で休憩1時間入り、7:30です。
18:30~22:00までは時間外労働時間、22:00~翌5:00までは深夜時間外です。例えば出勤時間10:00、退勤時間23:00とすると、時間外は3:30で深夜時間外は1:00です。
セルの縦列には
A:日付(例3/11で表示)
B:曜日
C:出勤時間(例10:00で表示)
D:退勤時間(例23:30で表示)
E:所定労働時間(7:30で固定で休憩の1時間を含んでいない)
F:時間外労働時間(例3:00で表示)
G:深夜時間外(例2:30で表示)
Q1:カレンダーの暦通り、曜日をBセルに自動表示させる方法はありますか?
Q2:Fセルの18:30~22:00までの時間外労働時間を計算する関数を教えて下さい。
Q3:Gセルの22:00~翌5:00までの深夜残業時間を計算する関数を教えて下さい。
Q4:出勤時間と退勤時間を入力すれば時間外と深夜時間外が表示されるようにしたいのですが可能ですか?
よろしくお願いします。
下記の回答を見つけてexcelで作成しましたが、22:00以降の深夜残業は計算で出るのですが
朝5:00前の出勤が計算されません。
朝5:00前の出勤が深夜残業に計算で加味される関数式が判りましたらご教示お願い致します。
例として1.朝の3:40出勤で25:20分退勤
2.朝の3:30出勤で14:30分退勤
の場合でお願いします。
>Q3:Gセルの22:00~翌5:00までの深夜残業時間を計算する関数を教えて下さい。
G2=IF(OR(C2="",D2=""),"",MAX(D2+(D2<C2),22/24)-22/24)
※D2+(D2<C2) C2=10:00で、D2=5:00の場合にD2=27:00とします。
※22/24="22:00"=22時のこと
※17/48=8.5/24="08:30"=8時間半(休憩時間を含む所定労働時間)のこと