Excelの勤怠管理シートの休憩時間

質問

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

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

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

定時、時間外、深夜　と枠別に集計値を出すのですが
それらから、休憩時間を引きたいと思っています。

定時がもっとも多いときは、定時から、
深夜勤が多いときは深夜勤から、休憩時間をマイナスには
どうしたらいいでしょうか？
（定時、時間外、深夜の集計には、”1：00”のように時間を使用しています。）

回答No.2

MackyNo1 · ベストアンサー

提示された「定時」の数式は、少なくとも開始時間が7：00などの場合に正しい値を返しません。

以下のように変更してください。

=(D7<--"18:00")*(MIN("18:00",F7)-MAX("9:00",D7))

実際の定時のセルに入れる数式は、例えば以下のようになります。

=(D7<--"18:00")*(MIN("18:00",F7)-MAX("9:00",D7))-(MAX(J7,K7,(D7<--"18:00")*(MIN("18:00",F7)-MAX("9:00",D7)))=(D7<--"18:00")*(MIN("18:00",F7)-MAX("9:00",D7)))*IF(SUM(J7,K7,(D7<--"18:00")*(MIN("18:00",F7)-MAX("9:00",D7)))>="8:00"*1,"1:15"*1,IF(SUM(J7,K7,(D7<--"18:00")*(MIN("18:00",F7)-MAX("9:00",D7)))>="6:00"*1,"1:00"*1,0))

お礼

ありがとうございます！
先日回答させていただいた後、定時の数式ミスと、教えていただいた数式を入力ミスしていたことに気がつきました・・・スイマセン。
休日と平日で残業時間の扱いがかわったりするので、教えていただいたことをベースに考えたいと思います。
またお世話になります。よろしくお願いします。

回答No.1

MackyNo1

定時、時間外、深夜の時間を集計する数式はできているのですね。

その場合は、定時のセルなら、以下のような数式になります。

=定時の数式-(MAX(時間外のセル,深夜のセル,定時の数式)=定時の数式)*IF(SUM(時間外のセル,深夜のセル,定時の数式)>="8:00"*1,"1:15"*1,IF(SUM(時間外のセル,深夜のセル,定時の数式)>="6:00"*1,"1:00"*1,0))

＃一般的に、このようなご質問では、使用している数式や実際のセル番地を提示されたほうが、的確で簡略な回答になります。

お礼

ありがとうございました。

早速、試してみたのですが、定時枠からマイナスされるべきところで、無反応です。自分で作ってみた式も同じ症状だったのですが、そもそもの数式が間違ってるのでしょうか？

補足

ありがとうございます。

横並びに
開始時間D列、終了時間E、終了時間の修正（日をまたがる場合があるので）E列、定時I、時間外K、深夜Mにそれぞれ入力しています。
休憩時間は考慮せず出した数字がはいっているので、隣の列に休憩時間を調整後の数字を入れたいと思っています。
数式は、
定時= (D7<--"18:00")*(MIN("18:00",F7)-D7)
時間外= (D7<--"9:00")*(MIN("9:00",F7)-D7)+(F7>--"18:00")*(MIN("22:00",F7)-"18:00")+(F7>--"29:00")*(F7-"29:00")
深夜=(F7>--"22:00")*(MIN("24:00",F7)-"22:00")+ (F7>--"24:00")*(MIN("29:00",F7)-"24:00")
です。