Excelの勤怠管理シートを

質問

休憩時間の入力方法で悩んでいます。

勤務時間の枠は下記のとおりです。
定時　9：00～18：00
時間外　18：00～22：00、5：00～9：00
深夜　　22：00～5：00

休憩は6時間以上勤務で1：00、8時間以上勤務で1：15
とらないといけないのですが、
労働時間が多かった枠で取得しなければなりません。

定時、時間外、深夜　と枠別に集計をかけていますので、
それの合計や、最大値などを使って数式を作ってみましたが
うまくいきませんでした。

ご回答お待ちしております。よろしくお願いします。

回答No.1

patak_m23d

EXCELシート内で
日付：B３　曜日：C3
作業開始時間：D3（時）、F3（分）
作業終了時間：G3（時）、I3（分）　　のとき

作業時間：J3（時）の計算式

　　=IF(D3="","",IF(G3+I3/60-D3-F3/60 < 0,IF(I3-F3<0,G3+23-D3,G3+24-D3),IF(I3-F3<0,G3-1-D3,G3-D3)))

作業時間：K3（分）の計算式

　　=IF(D3="","",IF(I3-F3<0,I3+60-F3,I3-F3))

休憩時間：L3（時間）の計算式

　　=IF(J5="","",IF(D5>=13,0,IF(G5>=26,3,IF(G5>=23,2,1))))

休憩時間：M3（分）の計算式

　　=IF(L3="","",IF(G3="","",IF(G3>=18,IF(G3>=23,0,30),30)))

実動時間：N3（時）の計算式

　　=IF(D3="","",IF(K3-M3<0,J3-1-L3,J3-L3))

実動時間：P３（分）の計算式

　　=IF(D3="","",IF(IF(K3-M3<0,K3+60-M3,K3-M3)>=45,45,IF(IF(K3-M3<0,K3+60-M3,K3-M3)>=30,30,IF(IF(K3-M3<0,K3+60-M3,K3-M3)>=15,15,0))))

(1)開始時間＜９：００＞　終了時間＜１９：００＞の場合
（休憩：昼の１：００、夕方の０：３０）
　作業時間＜１０：００＞
　休憩時間＜１：３０＞
　実動時間＜８：３０＞

(2)開始時間＜９：００＞　終了時間＜２３：００＞の場合
（休憩：昼の１：００、夕方の０：３０、２２：００以降の０：３０）
　作業時間＜１４：００＞
　休憩時間＜２：００＞
　実動時間＜１２：００＞

(3)開始時間＜９：００＞　終了時間＜３２：００＞の場合（徹夜で朝の８：００まで勤務）
（休憩：昼の１：００、夕方の０：３０、２２：００以降の０：３０、２６時（２時）以降１：００）
　作業時間＜１４：００＞
　休憩時間＜２：００＞
　実動時間＜１２：００＞

ちょっと難しいかもしれませんが、開始時間、終了時間からそれぞれの作業時間、休憩時間、実動時間を
計算すればいいです。
休憩時間の計算を修正することで、休憩の追加、変更は可能です。

お礼

ありがとうございました。
補足に記載させていただきましたが、枠別の数値が必要なのです…
よろしくお願いします。

補足

定時、時間外、深夜はそれぞれの枠に記載して集計しなければなりません。
休憩は合計時間からではなく、定時なら定時、深夜なら深夜の枠からマイナスしなくてはならず、最大値設定などうまく使えないかな、と思っています。
計算式は、1：15といった記載で入れています。
定時= (D7<--"18:00")*(MIN("18:00",F7)-D7)
みたいな感じです。