エクセルの勤務表計算の関数を教えて下さい。

会社の勤務表の入力を改良したいのですが、うまくいきません。
詳しい方教えていただけると助かります。宜敷願いします。

まず、勤務時間のトータル時間数が６時間以下の時は休憩は0時間。
17:30迄なら1:00。
17:31～18:00迄は１時間＋17:30を超過した分を１分単位でプラスする。
例えば、17:45なら1:15。
18:00を過ぎたら1:30という感じに休憩の欄に表示がしたいです。
Ｂ1に出勤時間、Ｃ1に退勤時間、Ｄ1に休憩時間という入力表になっています。
宜しくお願いします。

No.3 ベストアンサー 回答者： minosennin 回答日時： 2014/11/10 11:43

一例です。

D1に
=IF(B1="",0,IF(C1-B1<=6/24,0,IF(C1<=TIMEVALUE("17:30"),"1:00",IF(C1<=TIMEVALUE("18:00"),"1:00"+C1-TIMEVALUE("17:30"),"1:30"))))

なお、この式はこの画面からexcelへコピペできます。

この回答へのお礼

今日、早速勤務表に関数を入力してみました。
私が、表示したかった通りの表示が出来ました。本当にありかどうございました。とても助かりました。仕事を引き継いだばかりで、勤務表が全てベタ打ちなのをなんとかしようと思ったのですが、自分ではうまく出来なくて困っていました。ありがとうございます。

お礼日時：2014/11/10 22:53

No.4 回答者： bluelake 回答日時： 2014/11/10 12:02

改良したいのなら、今の状態と問題をはっきりさせること、ルールを整理することです。

>勤務時間のトータル時間数が６時間以下の時は休憩は0時間

>17:31～18:00迄は１時間＋17:30を超過した分を１分単位でプラスする。
>例えば、17:45なら1:15。
>18:00を過ぎたら1:30という感じに休憩の欄に表示がしたいです。


休憩時間には、昼休憩と定時後休憩があり、
在社時間（退社時刻-出社時刻）が7時間時間以上の人には昼休憩が1時間
定時が17時30分で、退社時刻が18時過ぎの人には、定時後休憩が30分ということでしょうか？

複雑な計算を関数で処理しようとすると、とても複雑な計算になってしまいます。
中間のセルを設けて頭で理解できるようにしたほうがよいと思います。
まずは、昼休憩と定時後休憩を分けて計算したらどうでしょうか？
複雑になりますが、後で統合することも可能です。

たとえば、昼休憩は、
=IF((C1-B1)<(6+1)/24,0,1/24)
※式の意味は、勤務時間の6時間に休憩の1時間を足した7時間に足りなければ昼休憩なし、足りたら1時間

定時後休憩は、
=IF(C1>18/24,0.5/24,IF(C1<17.5/24,0,(C1-17.5/24)))
※式の意味は、18:00を過ぎたら30分、17:30前なら0、その間は退社時刻と17:30分の差

No.2 回答者： bin-chan 回答日時： 2014/11/10 02:14

差引時間の計算は、同一日付内なら単に大きい時刻から小さい時刻を引算するだけ。

> 17:30迄なら1:00。
もしも17:30が勤務時間を言っているなら、
日付＋時刻で入力して、大きい日時から小さい日時を引算する。
このとき、24時間以上の勤務時間の可能性があるなら
セルの書式設定で”[h]:mm”と、時間(Hour)の部分をカギ付カッコで囲んで下さい。

No.1 回答者： bin-chan 回答日時： 2014/11/10 02:00

> うまくいきません。

どう「うまく」いかない？

> 17:30迄なら1:00。
勤務時間が？終業時刻とは思うが。
始業時刻に関して何も触れられて無いが、
11:30～17:30の勤務だったら、付与されるのは０時間？それとも１時間？

条件をもっときっちり整理したほうが良いですよ。
時間と時刻も使いわけしてね。