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

質問

No.1468024の質問をした者です。
勤務表を作成しています。（Excel2002を使用）
終了時刻（D列）ー開始時刻（C列）ー休憩時間（E列）=当日の勤務時間を計算しています。

No.1468024の質問でアドバイスをいただき、以下のような計算式を記述しました。
＜例＞
=AND(C13*D13)*(MIN(IF(AND(D13>="12:00"*1,D13<="13:00"*1),"13:00",D13),"17:40")-MAX(IF(AND(C13>="12:00"*1,C13<="13:00"*1),"12:00",C13),"09:00")-E13+FLOOR(MAX(D13,"18:00")-"17:59:59","0:15"))

ほぼうまくいくのですが、開始時刻9:00終了時刻12:00とした場合、勤務時間4:00となり、間違ってしまいます。（正しくは勤務時間3:00。また開始時刻9:00終了時刻が12時から13時までの間の時刻で入力された場合も勤務時間3:00と出力したいです。）

休憩時間セルに以下のような計算式を書いています。
＜例＞
=IF(COUNT(C13:D13)<2,0,IF(D13-"12:00"*1<=0,0,IF(C13-"12:00"*1>=0,0,IF(D13<"18:00"*1,"1:00","1:20"))))

休憩時間は12時から13時までの1時間。
終了時刻が17:40以降なら20分休憩時間が加算されます。
開始、終了が入力されていないと0:00表示。

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

回答No.4

ta123 · ベストアンサー

勤務時間の計算についてです。
現在の計算方法は［定時までの勤務時間］＋［残業時間］です。
これは定時までは1分刻み、残業時間は15分刻みでの計算という規則により２つに分ける必要があるためです。

■［定時までの勤務時間］
［終了時刻］ー［開始時刻］－［昼の休憩時間］で求めますが、補正が必要です。

(1)［終了時刻］
12:00－13:00の場合は12:00、17:40以降の場合は17:40とします。それ以外は終了時刻(D列)そのままです。
以下の式を確認してみて下さい。（等号については検討要）

MIN(IF(AND(D13>="12:00"*1,D13<="13:00"*1),"12:00",D13),"17:40")

(2)［開始時刻］
9:00以前に対しては9:00、12:00－13:00の場合は13:00とします。それ以外は開始時刻(C列)そのままです。
以下の式を確認してみて下さい。（等号については検討要）

MAX(IF(AND(C13>="12:00"*1,C13<="13:00"*1),"12:00",C13),"09:00")

(3)［昼の休憩時間］
E列には17:40からの休憩時間も含まれていますので無条件には使えません。ここは素直にE列で使った式中の昼休みの計算部分を使うしかなさそうです。（この箇所が間違っていた。）

IF((C13<"12:00"*1)*(D13>"13:00"*1),"1:00","0:00")

■［残業時間］
[終了時刻]ー18:00で求めた時間を15分刻みにします。
00(分)－14(分) ==> 00(分)
15(分)－29(分) ==> 15(分)
30(分)ー44(分) ==> 30(分) ・・・

残業していないケースをIF文を使わずに計算したいため終了時刻が18:00以前の場合は18:00と補正して、計算結果が00分になるように細工します。

FLOOR(MAX(D13,"18:00")-"17:59:59","0:15")
※"17:59:59"は18:00:00でもいいような気がしますが、元の式を尊重します。

ということで、勤務時間を求める計算式は以下のようになります。

MIN(IF(AND(D13>="12:00"*1,D13<="13:00"*1),"12:00",D13),"17:40")
-MAX(IF(AND(C13>="12:00"*1,C13<="13:00"*1),"13:00",C13),"09:00")
-IF((C13<"12:00"*1)*(D13>"13:00"*1),"1:00","0:00")
+FLOOR(MAX(D13,"18:00")-"17:59:59","0:15")

お礼

お礼の欄ですが、その後を書かせていただきます。
あれからチェックしていたところ、15分単位で切り捨てでなく、15分単位で切り上げて計算させなければならないということが発覚しました。
長いこと勘違いして作成していました…。本当にすみません。
結局開始、終了時刻を15分単位で切り上げるセルを作成し、計算させました。
これで本当に完了です。教えていただいた方々に改めて感謝いたします。

■9時-17時40分（定時）の場合
開始時刻：C列
終了時刻：D列
開始時刻を15分単位で切り上げる：P列
終了時刻を15分単位で切り上げる：Q列

■休憩時間
＜例＞
=IF(COUNT(C13:D13)<2,0,IF((P13<"12:00"*1)*(Q13>"13:00"*1),"1:00",
"0:00"))+IF(D13>"17:40"*1,"0:20","0:00")

■当日・勤務時間
＜例＞
=AND(C13*D13)*(MIN(IF(AND(D13>="12:00"*1,D13<="13:00"*1),"12:00",
Q13),"17:40")-MAX(IF(AND(C13>="12:00"*1,C13<="13:00"*1),"13:00",
P13),"09:00")-IF((C13<"12:00"*1)*(D13>"13:00"*1),"1:00","0:00")+
FLOOR(MAX(D13,"18:00")-"17:59:59","0:15"))

補足

以下のように計算式を書いてみました。
=AND(C13*D13)*(MIN(IF(AND(D13>="12:00"*1,D13<="13:00"*1),"12:00",
D13),"17:40")-MAX(IF(AND(C13>="12:00"*1,C13<="13:00"*1),"13:00",
C13),"09:00")-IF((C13<"12:00"*1)*(D13>"13:00"*1),"1:00","0:00")+
FLOOR(MAX(D13,"18:00")-"17:59:59","0:15"))

計算が合わないと言っていた時刻もそれぞれ正しく計算されました。

開始9:00-終了12:30（勤務3:00、休みなし）
開始9:00-終了18:15（勤務7:55、休み1:20）
開始9:00-終了18:30（勤務8:10、休み1:20）

勤務表を作り始めた時は
9:00以前の開始時間を9:00と考えさせる／12-13時の時間を13時と考え
させるというセルを作り、当日勤務時間の計算式でそのセルを指定して
ましたが、もう必要ではなくなりましたね。

＜例＞
P13に書いていた計算式
=IF(C13=" ","",IF(C13-"9:00"*1<=0,TIME(9,0,0),IF(AND(C13>="12:00"*1,"13:00
"*1>=C13),TIME(13,0,0),C13)))

本当に勉強になりました。
次はまた違うパターンで作らねばならない（8:30-17:05が定時の場合）
ので、ここを参考にしながら作成します。

ta123さん、本当にありがとうございました。感謝します。

回答No.3

ta123

休憩時間に入る値に解釈誤りがありました。（17:40からの20分の休憩時間が加算されていることは考慮していないため不正な値になる。前からですね）

休憩時間の計算式について

> 開始14:40-終了19:40
> （勤務4:30※残業1:30を含む、休みなし）

とありますが、「0:20休み」ですよね。

この場合の式ですが、考え方として
まず、昼休みの1時間は「開始時刻が12:00前で、かつ、終了時刻が13:00より後」の時に計上し、定時後の20分は「終了時刻が18:00以降」のとき計上するという条件かと思います。（開始時刻は常に17:40より前が前提条件にありますが）

IF((C13<"12:00"*1)*(D13>"13:00"*1),"1:00","0:00")+IF(D13>"18:00"*1,"0:20","0:00")

※条件に「=」がつくか否かは最終的に検証してください。

※勤務時間は、この休憩時間が確認できてからにしたいと思います。

お礼

ta123さん、回答ありがとうございます。

＞開始14:40-終了19:40（勤務4:30※残業1:30を含む、休みなし）
とありますが、「0:20休み」ですよね。

はい、そうです。書き間違いでした。

回答を参考に休憩時間を以下のような形で書いてみました。
=IF(COUNT(C13:D13)<2,0,IF((C13<"12:00"*1)*(D13>"13:00"*1),"1:00","0:00"))+IF(D13>"18:00"*1,"0:20","0:00")

正しく計算されています。
ta123さんの言われる条件で間違いないと思います。
例えば、遅刻で17:40以降出社（開始）は有り得ないので。

回答No.2

ta123

以下のようにすればよろしいかと。（再チャレンジさせてください）

=AND(C13*D13)*(MIN(IF(AND(D13>="12:00"*1,D13<="13:00"*1),"12:00",D13),"17:40")-MAX(IF(AND(C13>="12:00"*1,C13<="13:00"*1),"13:00",C13),"09:00")-E13+CEILING(MAX(D13,"18:14")-"18:14:00","0:15"))

終了時刻を決める部分
(MIN(IF(AND(D13>="12:00"*1,D13<="13:00"*1),"13:00",D13),"17:40")
で12:00～13:00のときに13:00にしていますが、12:00の間違いですね。

質問にはありませんでしたが、開始時刻を決める部分
MAX(IF(AND(C13>="12:00"*1,C13<="13:00"*1),"12:00",C13),"09:00")
で12:00～13:00のときに12:00にしていますが、13:00の間違いですね。元の数式では13:00→17:40のとき休憩時間(1:00)を入れないと1時間多くなります。

残業時間の計算部分はFloor関数からCeiling関数に変えてみました。これの方が理解し易いと思います。

お礼

ta123さん、回答ありがとうございます。
教えていただいた計算式を記述してみました。
開始時刻9:00とした場合、終了時刻18:15からの計算がうまくいかないようです。
補足の欄に書きます。

補足

新しい計算式を書いて、テストをしてみました。

計算式が合っていた時刻
開始9:00-終了17:40（勤務7:40、1:00休み）
開始9:00-終了12:00（勤務3:00、休みなし）
開始13:00-終了17:40（勤務4:40、休みなし）
開始12:41-終了17:40（勤務4:40、休みなし）
開始9:00-終了17:50（勤務7:40、1:00休み）
開始14:40-終了19:40（勤務4:30※残業1:30を含む、休みなし）

計算式が合わなかった時刻
開始9:00-終了12:30（勤務2:00、1:00休み）
正しくは（勤務3:00、休みなし）
開始9:00-終了18:15（勤務7:35、1:20休み）
正しくは（勤務7:55、休み1:20）
開始9:00-終了18:30（勤務7:50、1:20休み）
正しくは（勤務8:10、休み1:20）

元々の休憩時間の計算式が間違っているのでしょうか？
=IF(COUNT(C12:D12)<2,0,IF(D12-"12:00"*1<=0,0,IF(C12-"12:00"*1>=0,0,IF(D12<"18:00"*1,"1:00","1:20"))))

計算式に対しての条件が多すぎるため、このような計算がうまくいかない状態が出ていると思うのですが…。本当にすみません。

回答No.1

DoragonFang

勤務時間を計算している式で、12:00を含まなければいいのでは内でしょうか？
具体的には、

＜例＞
=AND(C13*D13)*(MIN(IF(AND(D13>"12:00"*1,D13<="13:00"*1),"13:00",D13),"17:40")-MAX(IF(AND(C13>"12:00"*1,C13<="13:00"*1),"12:00",C13),"09:00")-E13+FLOOR(MAX(D13,"18:00")-"17:59:59","0:15"))

で、>="12:00" の部分の=を取り除くだけです。

お礼

DoragonFangさん、回答ありがとうございます。
ta123さんが指摘されている通り、休憩時間の計算式を修正した上で、>="12:00" の部分の=を取り除いてもうまく計算されませんでした。

すみません。頭がガチガチで…。