Excel：当日勤務時間の計算式のアドバイスを下さい

質問

Excel2002を使用し、勤務表を作成しています。
修正を繰り返しだいぶ計算ができるようになりました。
今回は、開始13:15終了16:15の場合、休憩なし、当日勤務時間3時間と計算させたいのですが、何かアドバイスあればよろしくお願いします。

現在使用している計算式では以下のように計算されます。
開始8:30終了17:05、休憩45分、当日勤務時間7時間50分※定時
開始7:30終了16:05の場合、休憩45分、当日勤務時間7時間50分
開始8:00終了16:35の場合、休憩45分、当日勤務時間7時間50分
開始13:15終了17:05の場合、休憩なし、当日勤務時間3時間50分
開始13:15終了17:50の場合、休憩なし、当日勤務時間3時間50分
--------------------------------------------------------
開始時刻：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",IF
((C6>="13:15"*1)*(D6>="17:20"*1),"0:15","0:00")))+IF(R6>="8:00"*1,"0:15","0:00")

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

■休憩時間を45分として勤務時間を計算する
=IF(COUNT(C6:D6)<2,0,AND(C6*D6)*(MIN(IF(AND(D6>="12:30"*1,D6<="13:15"*1),"12:30",Q6),"16:05")-MAX(IF(AND(C6>="12:30"*1,C6<="13:15"*1),"13:15",P6),"07:30")-IF((P6<"12:30"*1)*(Q6>"13:15"*1),"0:45","0:00")+FLOOR(MAX(D6,"16:05")-"16:04:59","0:15")))

回答No.4

ima25

ima15イコールima25です。
ima15のパスワードを忘れてしまったため、この質問を締め切ることが出来ない状態です。
（パスワード問い合わせフォームでもうまく本人確認ができず、分からずじまい…。）
大変申し訳ありません。
パスワードが分かり次第、すぐに締め切ります。
（05.12.22 記）

回答No.3

takkuni

ご質問の意味の理解不足かも分かりませんが、難しく考えすぎのような気がします。
休憩時間は拘束時間を元に考えた方がわかりやすいのでは。
休憩の時間帯もデータとして残すなら別ですが、4時間未満なら休憩無し、定時なら45分、残業などで定時以上なら1時間とか。
開始時刻と終了時刻はその都度入力して差引きする事として、IF関数で入れ子を使えば関数も簡単になると思いますが。

お礼

アドバイス、ありがとうございます。takkuniさんの言われるように、難しく考えすぎとも思います（トホホ）
次々追加したためにごちゃごちゃになっていると思います。
アドバイス、参考させていただきます。

回答No.2

banker_U

先ほどのアドバイス２の補足です。
正直まだ理解できていないのですが、
　始業時間は8:30を基準に、15分単位で切り上げ。
　終業時間は17:20を基準に、15分単位で切り捨て。
ってことでいいですよね。
であれば、

「勤務時間」と「休憩時間」という２つのシートを新たにつくり、それぞれに、
　縦軸に始業時間(0:00,・・・,7:30,7:45,8:00,8:15,・・・)
　横軸に終業時間(0:05,・・・15:05,15:20,15:35,15:50,・・・,23:50)
を入力しておきます(別にありえない分は入力する必要ないですが、その場合下記のOFFSET関数に若干の工夫が必要)。

次に、それぞれ始業時間と終業時間の交わるところにその場合の勤務時間(休憩時間)を入力しておきます。
(たくさんあるので、結局計算式を使うことになると思いますが、目で見ながらそこにあった数式を入れていけばいいので、簡単だと思います)

この場合、
勤務時間は下記で出てくると思います(休憩時間も同様)。
=OFFSET(勤務時間!$A$1,始業時間(*)/timevalue("0:15"),(終業時間(*)-timevalue("0:05"))/timevalue("0:15"))

(*)始業時間と終業時間は、最初の前提で丸めたもの。つまりこのまま書くのではなく、あなたの例だとP列Q列に入っているデータになるのかな？(それにしては若干数式がおかしいと思いますが)。

この方法のメリットは、要するにあらかじめあらゆるパターンの計算をしておくということだから、とにかく表を作っていくうちに頭が整理されてきて、結局うまい数式が思いつくかもしれないという点です。それにあとから追加の要望が出てくる余地もないですよね。

では頑張ってください。

お礼

再度のアドバイス、ありがとうございます。アドバイスを参考に再考します。

回答No.1

banker_U

【補足要求】そもそも貴社の勤務時間の計算ルールはどうなっているんですか？
前回の質問内容も拝見しましたが、何をしたいのかがよくわかりません。
【アドバイスその１】
一度計算ルールを箇条書きにしてみてください。
あるいは、フローチャートの方がいいかもしれません。
あとはそれを数式に落としていくだけです。
【アドバイスその２】
わたしの思うに、15分刻みでしか計算しないのなら、
あらかじめ、求める時間を計算したパターンテーブルを作っておいて、OFFSET関数でそれを参照に行った方が、あとあと便利なような気がします。

お礼

書いていただき、ありがとうございます。
【アドバイスその１、その２】を参考に一度考え直してみます。

補足

言葉足らずで大変申し訳ありません。以下のようなルールです。

基本の勤務時間は
開始8:30終了17:05、休憩45分、当日勤務時間7時間50分です。

早く出勤することもあり、その場合以下のように考えます。
開始7:30終了16:05、休憩45分、当日勤務時間7時間50分
開始8:00終了16:35、休憩45分、当日勤務時間7時間50分

お昼12:30～13:15の45分休みをとる。
終了時刻が17:05以降なら17:05～17:20の15分休みをとる。
（45分に加算。結果、休憩60分）
午後出勤（13:15～）で残業なら、17:05～17:20の15分休みをとる。

また、終了17:20から15分切り捨てで勤務時間をカウントする。

以下のような勤務というのはほぼ有り得ないが、
開始13:15終了16:15の場合、休憩なし、当日勤務時間3時間
（現在の計算式では開始13:15終了16:15の場合、休憩なし、当日勤務時
間2時間50分。）

開始13:15終了14:15の場合、休憩なし、当日勤務時間1時間
開始13:15終了15:15の場合、休憩なし、当日勤務時間2時間
となり、終了16:15の場合も同じように計算させたいという要望があり、調べています。
作っているうちにどんどん要望が追加され、混乱しているのも事実です。