Excel：特定の時刻から15分単位で表示させるには？

質問

勤務表を作成しています。
終了時刻ー開始時刻ー休憩時間という具合に当日の勤務時間を出力させるようにしています。

当日の勤務時間を出力するセルには以下のような条件で計算式を書いています。

＜例＞
=IF(COUNT(C13:D13)<2,0,IF(AND(C13<="9:00"*1,D13>="17:40"*1,"18:1
4"*1>=D13),"7:40"*1,IF(AND(C13>="12:00"*1,"13:00"*1>=C13,D13>="1
7:40"*1),"4:40"*1,IF(AND(D13>="12:00"*1,"13:00"*1>=D13),"3:00"*1,
FLOOR(D13-P13-E13,"0:15")))))

(1)開始、終了時刻が入力されていない時、0:00と表示。
(2)開始時刻9:00（もしくはその時刻より前）、
終了時刻17:40から18:14の間の時刻で入力された時、7:40と表示する。
(3)開始時刻12:00から13:00の間の時刻、
終了時刻17:40で入力された時、4:40と表示する。
(4)終了時刻12:00から13:00の間の時刻で入力された時、3:00と表示す
る。

(5)(1)～(4)の条件以外は終了時刻ー開始時刻ー休憩時間という計算を
行い、15分単位で丸める。

(1)～(4)の条件は変更なしで、(5)の条件を変えます。

(5)終了時刻が18:15以降、当日勤務時間が7時間40分以上になる。
7時間40分から15分単位で丸める。
＜例＞
開始時刻9:00、終了時刻17:40の時、勤務時間7時間40分。（定時）
開始時刻9:00、終了時刻18:14の時、勤務時間7時間40分。
開始時刻9:00、終了時刻18:15の時、勤務時間7時間55分。
（一番上に書いたの計算式だと勤務時間が7時間45分になる）
開始時刻9:00、終了時刻18:16の時、勤務時間7時間55分。

※P13は9:00以前の開始時間を9:00と考えさせる、
12-13時の時間を13時と考えさせる、
上記以外はそのまま表示させるという計算式を書いたセル。

(5)のような条件を加えた計算式が可能であれば教えて下さい。（アドバイス、ヒントでもかまいません。）
よろしくお願いします。

使用ソフト：Excel2002

回答No.2

grumpy_the_dwarf · ベストアンサー

5番の条件変更で、休憩時間はお昼の1時間に固定され超過勤務以外は
15分で丸めないことになったとします。

=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")
　-"1:00"
　+FLOOR(MAX(D13, "18:00")-"17:59:59", "0:15"))

E列に「お昼の休憩以外に休んだ時間」を書かせて引いてやるとか、
遅刻や早退の分も15分単位で丸めるなどの処理は適当に追加してやっ
て下さい。

お礼

grumpy_the_dwarfさん、ありがとうございます。
当日勤務時間のセルに教えていただいた計算式を記述したところ、このように出力させたい！と思っていた通りに計算されました。

時間を割いて、回答していただきありがとうございました。

教えていただいた計算式をしっかり見て、勉強させていただきます。

回答No.1

ta123

考えてみたのですが一度に求める数式を書く自信がなかったので、作業列を設けて段階的に作ってみました。

C列：開始時刻、D列：終了時刻、E列：休憩時間ですよね。
15分単位で丸めるのは18:15以降の勤務に対してで切り上げですよね。（それとも勤務時間が7時間40分を超えたときでしょうか。9:00開始以外のケースが不明）

計算上の開始時刻（G列)
：=IF(COUNT(C13:D13)=2,IF(C13<=TIME(12,0,0),MAX(C13,TIME(9,0,0)),MAX(C13,TIME(13,0,0))),TIME(0,0,0))

計算上の終了時刻（H列)
：=IF(COUNT(C13:D13)=2,IF(D13<TIME(13,0,0),MIN(D13,TIME(12,0,0)),IF(D13<=TIME(18,14,0),MIN(D13,TIME(17,40,0)),D13)),TIME(0,0,0))

定時内勤務時間（I列)
：=MIN(H13,TIME(17,40,0))-G13-E13

残業時間（J列)
：=IF(H13>=TIME(18,15,0),CEILING(D13-TIME(18,15,0),"0:15"),TIME(0,0,0))
　※18:15以降の勤務時間に対して15分単位に切り上げて丸めています。

1日の勤務時間
：=I13+J13

お礼

ta123さん、ありがとうございます。
別のセルに記述し、実行してみたところ、
開始時刻9:00、終了時刻18:15の時、勤務時間は7:20と表示されました。
これだと、定時で終了した時（定時は開始時刻9:00、終了時刻17:40、勤務時間7:40になります）より勤務時間が少ないことになってしまいます。

時間を割いて、回答していただきありがとうございました。