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から順に入れる関数を詳しく教えてください。

と、過去に質問をしたところ本当に詳しく回答して頂きたちまち解決したのですが、夜間労働者のタイムカードで問題が発生してしまったのでどうか知恵をお貸しください。

問題というのは、今d1,e1,f1,g1,h1,i1セルに入力している関数だと上記の勤務パターンだとうまくいくのですが、例えば、
a1に出勤時間(18:00)を入力して
b1に退勤時間(30:00)を入力して
c1に休憩時間(1.00)を入力すると
d1に拘束時間→12.00
e1に労働時間→11.00
f1に日勤時間→3.00
g1に残業時間→0.00
h1に深夜時間→5.00
i1に深夜残業→3.00
と計算されてしまいます。
本当は朝5:00から6:00までの1時間は、深夜時間(22時から翌5:00)を抜けているので、
f1の日勤→3.00
g1の残業→1.00
h1の深夜時間→5.00
i1の深夜残業→2.00
となります。
こう計算されるようにする関数を今の関数から手直ししたいので教えてください。

今入力している関数は
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")-C1/24)*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))

です。
退勤時間の入力は、25:00,26:00方式です。
休憩は日勤時間でとるようになっていると思います。

どんな勤務体系でも正確に計算できるようにしたいです。
例えば、、
a1に出勤時間(22:00)を入力して
b1に退勤時間(32:00)を入力して
c1に休憩時間(1.00)を入力すると
d1に拘束時間→10.00
e1に労働時間→9.00
f1に日勤時間→1.00
g1に残業時間→1.00
h1に深夜時間→7.00
i1に深夜残業→0.00
と計算してもらえるようにです。

お願いいたします。
長文失礼しました。。

No.3 ベストアンサー 回答者： jmuiny 回答日時： 2016/07/01 16:31

G1を

=IF(COUNT(A1:B1)<2,"",(MAX(0,MIN(B1,"22:00")-A1)+MAX(0,B1-"29:00"))*24-C1-F1)
にすればいいように思います。

この回答へのお礼

jmuinyさん！またしてもありがとうございました！今後は自分でも勉強して知識をつけていきたいと思います。。

お礼日時：2016/07/03 01:59

No.2 回答者： 銀鱗 回答日時： 2016/07/01 09:43

休憩を入れた時間を入力するように変更すべきと思う。

例：
休憩始　5：00
休憩終　6：00

今の問題は深夜就業時間に休憩時間を取ったのか、分からないという点にあると思う。

…あと、シリアル値のまま計算することを強く勧める。
マイナスの時間になるとエラーになるでしょうが、そこはオプションで「1904年から計算する」を有効にすれば回避できます。

この回答へのお礼

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

お礼日時：2016/07/03 01:58

No.1 回答者： くんこば 回答日時： 2016/07/01 07:45

以前のメールでいくらか勉強はされました？回答の中にシリアル値という単語は出てきませんでしたか？その関数は自分で考えたもの？

今後のメンテも考え、少し勉強された方が良いですよ。

・日付はシリアル値という別の数値で管理されています。
・セルに時間を入力した場合、自動で今日の日付と共に入力されています。表示されていないだけです。

この回答へのお礼

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

お礼日時：2016/07/03 01:58