Excel：勤務時間計の計算式がうまくいかない

質問

Excel2002を使用し、勤務表を作成しています。
以前こちらでアドバイスをいただき、作成することができました。
今回修正で、開始8:30終了17:05の場合、休憩45分、当日勤務時間7時間
50分と計算させたいのですが、何かアドバイスあればよろしくお願いし
ます。

現在使用している計算式では以下のように計算されます。
開始8:00終了16:35の場合、休憩45分、当日勤務時間7時間50分
開始8:30終了17:05の場合、休憩60分、当日勤務時間7時間35分

■8時-16時35分（定時）の場合
開始時刻：C列
終了時刻：D列
開始時刻を15分単位で切り上げる：P列：=CEILING(C7,"0:15")
終了時刻を15分単位で切り上げる：Q列：=CEILING(D7,"0:15")

■休憩時間
=IF(COUNT(C7:D7)<2,0,IF((P7<"12:30"*1)*(Q7>"13:15"*1),"0:45","0:
00"))+IF(D7>="16:50"*1,"0:15","0:00")

休憩時間は12:30から13:15までの45分。
終了時刻が16:35以降なら15分の休憩時間が加算。
開始、終了が入力されていないと0:00表示。

■当日 勤務時間
=AND(C6*D6)*(MIN(IF(AND(D6>="12:30"*1,D6<="13:15"*1),"12:30",Q6),
"16:35")-MAX(IF(AND(C6>="12:30"*1,C6<="13:15"*1),"13:15",P6),"08
:00")-IF((P6<"12:30"*1)*(Q6>"13:15"*1),"0:45","0:00")+FLOOR(MAX
(D6,"16:50")-"16:49:59","0:15"))

開始、終了が入力されていないと空白
12:30～13:15の場合は12:30、16:35以降の場合は16:35、それ以外は終
了時刻のまま。
8:00以前は8:00、12:30～13:15の場合は13:15、それ以外は開始時刻の
まま。
残業していないケースをIF文を使わずに計算したいため終了時刻が
16:50以前の場合は16:50と補正して、計算結果が00分になるように細工
。

言葉足らずでしたら、進んで補足させていただきますので、よろしくお
願いします。

回答No.2

tascany · ベストアンサー

休憩時間を原則45分として計算したものを＜労働時間＞、
＜労働時間＞が8時間以上の場合にそこから15分を差し引いたもの（8時間未満の場合にはそのまま）を＜勤務時間＞とする（つまり、＜勤務時間＞は＜労働時間＞の関数である）、
というふうに理解していいですか？

それでいいなら、勤務時間フィールドと労働時間フィールドとを分けることをおすすめします。
ダイレクトに勤務時間を求めるとなると、式が異常に長くなるからです。

以下、修正を必要最小限にとどめたやり方をご紹介します。

■当日勤務時間→労働時間
「+FLOOR(MAX(D6,"16:50")-"16:49:59","0:15")」の部分を
「+FLOOR(MAX(D6,"16:35")-"16:34:59","0:15")」に変更する。
（「勤務時間」ではなく）「労働時間」を求める式になります。

■勤務時間（新たに列を作る）
労働時間を求める式が入ったセルを「●」とすると、
「＝IF(●>="8:00"*1,●-"0:15",●)」。
※●の部分に労働時間を求める式を入れると、ダイレクトに勤務時間を求める式になります。

■休憩時間
後半「+IF(D7>="16:50"*1,"0:15","0:00")」の部分を
「+IF(●>="8:00"*1,"0:15","0:00")」に変更。

お礼

回答ありがとうございます。
早速、教えていただいたことを試してみます。

補足

試した結果を報告します。
以下のように計算式を設定しました。

■8時-16時35分（定時）の場合
開始時刻：C列
終了時刻：D列
開始時刻を15分単位で切り上げる：P列：=CEILING(C6,"0:15")
終了時刻を15分単位で切り上げる：Q列：=CEILING(D6,"0:15")
休憩時間を45分として勤務時間を計算する：R列

■休憩時間
=IF(COUNT(C6:D6)<2,0,IF((P6<"12:30"*1)*(Q6>"13:15"*1),"0:45","0:
00"))+IF(R6>="8:00"*1,"0:15","0:00")

■休憩時間を45分として勤務時間を計算する
（提案していただいた『労働時間』です）
=IF(COUNT(C6:D6)<2,0,AND(C6*D6)*(MIN(IF(AND(D6>="12:30"*1,D6<="1
3:15"*1),"12:30",Q6),"16:35")-MAX(IF(AND(C6>="12:30"*1,C6<="13:1
5"*1),"13:15",P6),"08:00")-IF((P6<"12:30"*1)*(Q6>"13:15"*1),"0:4
5","0:00")+FLOOR(MAX(D6,"16:35")-"16:34:59","0:15")))

■当日 勤務時間
=IF(COUNT(C6:D6)<2,0,IF(R6>="8:00"*1,R6-"0:15",R6))

うまく計算されています。
的確なアドバイス、本当にありがとうございました。
助かりました。

回答No.3

Dxak

え～っと、未だに読解できてませんが・・・。
tascanyさんの回答で出来上がりなら、この後は無視してください。

始業時間は
～ 8:00 は 8:00
8:00～12:30 は 入力時間
12:30～13:15 は 13:15
13:15～ は なし

終業時間は
～12:30 は なし
12:30～13:15 は 12:30
13:15～16:35 は 入力時間
16:35～16:50 は 16:35
16:50～ は 入力時間

休憩時間
12:30～13:15 の 0:45 が 休憩時間
勤務時間が 8:00を超えた場合
16:35～16:50 の 0:15 が 休憩時間追加

と、いう事かな？？？
以前と以降が誤植だとして読解してますけど、あってます？

お礼

再度書いてくださり、ありがとうございます。
tascanyさんの回答で計算できました。説明不足で申し訳ありません。

＞tascanyさんの回答で出来上がりなら、この後は無視してください。

とのことですので補足はしませんが、回答して下さる気持ちに感謝いたします。
ありがとうございました。

回答No.1

Dxak

すみません。条件が理解しがたいのですが・・・。

就業時間： 8:30～17:05 休憩時間： 45分 h： 7:50

にしたいと言う事ですが、

休憩時間は12:30から13:15までの45分
終業時刻が16:35以降なら15分の休憩時間が加算

と、相反するのですが、どうなのでしょうか？
連続労働、何時間以上になるので15分加算って感じなのでしょうか？？？

補足

＞終業時刻が16:35以降なら15分の休憩時間が加算

これは、今回の修正前までの条件になります。
言葉足らずで申し訳ありません。

＞連続労働、何時間以上になるので15分加算って感じなのでしょうか？？？

労働時間8時間以上で45分に15分の休憩が加算されます。
よって、定時の労働時間7時間50分ですと、休憩45分と計算させたいと考えています。

元の式からどういじったらよいものか困り果てております…。
アドバイスあれば、よろしくお願いします。