エクセル関数で時間の計算方法を教えて下さい。

質問

エクセルの関数に詳しい方、是非助けて下さい。

労働時間計算をエクセル関数を使って簡単に出来ないものかと考えてます。
一般就業時間は9：30から18：00までとして

1つ目　出勤9：30から退社18：00うちお昼休み1時間＝？時間
2つ目　18：00以降の残業がある場合、18：30を過ぎた場合は、＝全て30分
　　　　　 18：30を過ぎない18：20等の場合は＝0：20を表示する。
3つ目　出勤9：30からで退社20：00の場合は、＝一つ目の？時間-二つ目の30分-9：30から20：00までの時間

以上の内容を
セルA1に出勤時間　セルB1に退社時間　セルC1に1つめの値　セルD1に2つ目の値　セルE1に3つ目の値を出したいと思っています。

色々試したのですがうまくいかず大変困っています。
宜しくお願い致します。

回答No.3

KURUMITO · ベストアンサー

回答Ｎｏ2です。
Ｃ1セルへの入力の式は

=IF(COUNT(A1:B1)<>2,"",B1-MAX(A1,"9:30")-IF(A1<"12:00"*1,MIN(B1,"13:00")-MAX(A1,"12:00")))

上の式では12時以前に出勤しなければ1時間の休みはありません。

Ｄ1セルには次の式を入力します。

=IF(COUNT(A1:B1)<>2,"",IF(AND(B1>"18:00"*1,B1<"18:30"*1),B1-"18:00","0:30"))

Ｅ1セルには次の式を入力します。

=IF(COUNT(A1:B1)<>2,"",IF(B1>"18:30"*1,B1-"18:30",0))

お礼

ありがとうございます。想像通りに表示されました。
本当にありがとうございます。

回答No.2

KURUMITO

ご質問の意味がよく理解できません。Ａ1セルが9:30でＢ1セルが18：20のとき、18:50のとき、19：20のとき22：30のときにＣ1セル、Ｄ1セル、Ｅ1セルに表示される値はそれぞれのケースでどのようにしてほしいのですか？
具体的に示してください。

補足

説明不足で申し訳ありません。
C1には、休憩1時間を引いたA1とB2の合計労働時間

D1には、18：00から残業時間
ただし、最低が1分で最高を30にしたいので、
退社時間が18：15なら0：15で、18：03なら0：03
退社時間が18：30以上の場合は全て0：30になる様にしたいです。

E1には、18：30以降の残業時間合計を出したいと思っています。

A1が9：30でB1が19：30の時、C1は9：00、D1は0：30でE1は1：00という形です。
また、
A1が9：30でB1が18：15の時、C1は7：45、D1は0：15でE1は0：00という形です。

お手数をおかけして申し訳ございません。

回答No.1

yugere

C1：=TIME(18,,)-TIME(9,30,)-TIME(1,,)
　セルの書式設定（ユーザー設定：h:mm）
D1：=IF(B1>=TIME(18,30,),1/24/2,MINUTE(B1))
　セルの書式設定（ユーザー設定：h:mm）
E1：=(B1-A1)-(C1+D1)
　セルの書式設定（ユーザー設定：-h:mm）

で、ご希望に合いますでしょうか？

補足

早々の回答ありがとうございます。

C1の値は出ました。
しかし、
D1の値が18：00を超えた分の18：12等の場合に12と表示されづに0：00と表示されてしまいます。
また、E1の値がうまう表示されません。

こちらの画面では
A1を9：30としてB1を21：00とすると、C1は10：30でD1は0：30E1は0：30と表示されます。
E1には18：30以降の超過時間を表示したいとおもっています。

またB1が18：12の場合は、D1が0：00と表示されてしまいます。
エクセルは2010です。

どうしたらよいでしょうか。