Excel 関数

こんばんは。
雇用者のタイムカード管理をエクセルでしようと思うのですが、関数がわかりません。
例えば、
a1に出勤時間(11:00)を入力して
b1に退勤時間(23:00)を入力して
c1に休憩時間(1.00)を入力すると
d1に拘束時間→12.00
e1に労働時間→11.00
f1に日勤時間→8.00
g1に残業時間→2.00
h1に深夜時間→0.00
i1に深夜残業→1.00
と計算されるようにしたいのですが、
d1から順に入れる関数を詳しく教えてください。

8時間を超えた残業でも、深夜22時から翌5時までは深夜残業なので、分かれて計算されるようにしたいのです。

いろいろと試したのですが、例えば、8時間までは日勤みたいな計算にすると、
18時から24時勤務で休憩0時間の場合、6時間勤務なので日勤セルにただ6.00と出てしまい、
本当は日勤4時間で深夜2時間なのに本来の計算が出来ません。

本当に困っていまして、、
詳しい関数や、こういう項目列を増やせばいい。とかこれはいらない。とか教えて頂ければ幸いです。
一番正確でシンプルな関数や方法をご享受ください。
よろしくお願いいたします。

質問者からの補足コメント

• すみません。
  説明不足でした。
  深夜時間というのは8時間を超えず、22時〜翌5時の間で働いた時間(1.25倍)で、
  深夜残業というのは8時間を超えて、22時〜翌5時の間で働いた時間(1.5倍)のことです。
  なので深夜時間+深夜残業=その人が22時〜翌5時の間で働いた本当の時間ということになります。

  No.1の回答に寄せられた補足コメントです。 補足日時：2016/06/28 17:12

• jmuiny様、丁寧な御回答有難うございました！
  早速試作したところ、うまくいきそうです。
  ただ一つだけ問題がありまして、
  出勤を14:30、退勤を21:30、休憩を1.00と入力したところ、
  拘束時間は7.00で合い。労働時間は6.00で合い。深夜時間、深夜残業ともに0.00で合いなのですが、
  日勤時間が6.00のところ7.00と計算されて、残業時間が0.00のところ-1.00と計算されてしまいます。
  どうか知恵をお貸しください。宜しくお願い致します。

  No.2の回答に寄せられた補足コメントです。 補足日時：2016/06/28 18:53

No.2 ベストアンサー 回答者： jmuiny 回答日時： 2016/06/28 18:13

D列からI列は書式は数値で小数点以下2にします。

D1
=IF(COUNT(A1:B1)<2,"",(B1-A1)*24)
E1
=IF(COUNT(A1:B1)<2,"",D1-C1)
F1
=IF(COUNT(A1:B1)<2,"",(MAX(0,MIN("8:00",MIN(B1,"22:00")-MAX(A1,"5:00")))*24))
G1
=IF(COUNT(A1:B1)<2,"",(MAX(0,MIN(B1,"22:00")-A1)*24-C1-F1))
H1
=IF(COUNT(A1:B1)<2,"",E1-F1-G1-I1)
I1
=IF(COUNT(A1:B1)<2,"",MAX(0,E1-8-G1))

すみませんが、詳しく説明は勘弁して下さい。
すべてに入っている　=IF(COUNT(A1:B1)<2　は入力しない場合を空白にする為です。
時間の計算は、どんな場合もマイナスがないのでMAX(0,計算式）が基本です。
IFを使わないでどんな時間でも対応できるようにするのが鉄則です。
理解できない部分だけ具体的に聞いて下されば説明はします。

No.3 回答者： jmuiny 回答日時： 2016/06/28 19:16

F1だけ修正です。

=IF(COUNT(A1:B1)<2,"",(MAX(0,MIN("8:00",MIN(B1,"22:00")-MAX(A1,"5:00")-C1/24)*24)))

休憩時間は通常時間内にとるというのを前提にしています。
休憩時間も1:00のように入力する方がいいと思いますが・・。

この回答へのお礼

解決しました！！大変助かりました！
本当にありがとうございます！！

お礼日時：2016/06/28 19:36

No.1 回答者： jmuiny 回答日時： 2016/06/28 13:26

質問文の例の時間で（11:00から23:00）

h1に深夜時間→0.00
ここが何故0なのか説明して頂けませんか。
22:00～23:00の1時間は深夜時間だと思いますが。
ここだけわからないので、回答できないのです。

この回答へのお礼

大変分かりやすく、丁寧な回答ありがとうございました(^^)
本当に助かりました。。
何度も言いますが、本当にありがとうございます！

お礼日時：2016/06/28 19:39