Excel：特定の条件でFLOOR関数を使うには？

質問

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

当日の勤務時間を出力するセルには以下の条件が必要です。

(1)開始、終了時刻が入力されていない時、0:00と表示する。

(2)開始時刻8:30（もしくはその時刻より前）、
終了時刻17:05と入力された時、7:50と表示する。

(3)(2)の条件以外は15分単位で勤務時間を表示する。

15分単位で丸めるということでFLOORを使うのですが、
そうすると(2)の条件がまったく無視されてしまい、うまくいきません。

FLOORを特定の条件の時のみ、適用する方法があれば教えて下さい。
よろしくお願いします。

使用ソフト：Excel2002

-------------------------------------------------
ちなみに以下のような計算式を当日勤務時間が出力されるセルに書いて試してみました。

=IF(D9="17:05","7:50",IF(COUNT(C9:D9)<2,"0:00",FLOOR(D9-P9-E9,"0
:15")))

計算式の意味↓
終了時刻に17:05と入力されたら、当日勤務時間のセルに7:50と表示。
開始、終了が入力されないと、0:00と表示。
そうでなければ、終了ー開始(8:30以前を8:30と考えるセル)ー休憩で計算し、15分単位で表示。

とこのように書いてみたものの…うまくいきませんでした。

回答No.1

maruru01 · ベストアンサー

こんにちは。maruru01です。

時刻（のシリアル値）の比較は、

D9="17:05"

という書き方では出来ません。
これだと単なる文字列の「17:05」と比較することになります。
右辺を時刻のシリアル値に変換してやればいいです。
変換方法は、TIMEVALUE関数を通してもいいし、何か数値を掛けたり足したり（四則演算）してやってもいいです。
値を変えないということで、1を掛けてやるといいでしょう。

D9="17:05"*1

で、あとは、条件(1)が最優先ということを考慮して、

=IF(COUNT(C9:D9)<2,0,IF(AND(C9<="8:30"*1,D9="17:05"*1),"7:50"*1,FLOOR(D9-C9-E9,"0:15")))

という感じになります。
（C9：開始、D9：終了、E9：休憩時間としました。）

お礼

迅速な回答ありがとうございます。
早速、計算式をセルに書いてみたところ、うまく計算されました！

文字列と比較させていたために、うまく計算できなかったんですね…。
勉強になりました。

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

回答No.2

mshr1962

C9=開始時刻,D9=終了時刻,E9=休憩時間として
=IF(AND(C9>0,C9<=17/48,TRUNC(D9*288)=205),47/144,FLOOR(MAX(D9,17/48)-MAX(C9,17/48)-E9,1/96)
)

開始時間が8:30以前で終了時間が17:05なら7:50
上記以外は終了時間が8:30以前なら00:00
8:30以降なら開始時間(8:30以前を8:30と考えるセル)-休憩時間

お礼

早速の回答ありがとうございます。
mshr1962さんの計算式でも試してみたところ、うまく計算されました。

同じ結果を出すときでも、違う書き方があるんだなーと改めて勉強になりました。

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