勤務時間の計算（定時間内、残業の割り振り）について

質問

他の質問を確認しましたが、当てはまるものが見つかりませんでした。すみませんが教えてください。
勤務時間の計算で、定時間内分、時間外分の割り振り方を教えてください。
定時間内勤務は8:30～17:30（休憩通常1H、通常8時間勤務)です。
8:30～17:30は定時間、それ以外は時間外とします。
割り振りは1日ごとに行い、範囲は0:00～24:00とします。
入力は下のようにします。
A1：開始時間　B1：終了時間　C1：定時間内休憩　D1：時間外休憩　E1：定時間内　F1：時間外　G1：休日
A1、B1は[hh]:mmで入力（15分単位）。C1～F1は0.00(H)を0.25(15分単位）単位で入力または表示します。
たとえば、A1:7:30、B1:22:30、C1：1.0、D1：0.5のとき、E1：8(H)、F1：5.5(H)のように、E1とF1を自動で計算させたいのです。
G1は休日フラグとし、0なら平日で時間内がある、1なら全て時間外とします。
勤務は不規則で以下のようなケースもあります。
●定時間にかからないケース(1)↓
A1=1:00、B1=8:00、C1=0、D1=0.75、G1=0のときE1=0、F1=6.25
●定時間にかからないケース(2)↓
A1=17:30、B1=23:30、C1=0、D1=0、G1=0のときE1=0、F1=6
●定時間にかかるケース(1)↓
A1=5:00、B1=15:15、C1=0.75、D1=0、G1=0のときE1=6、F1=3.5
●定時間にかかるケース(2)↓
A1=11:00、B1=24:00、C1=1.0、D1=0.5、G1=0のときE1=5.5、F1=6
●定時間にかかるケース(3)↓（極端な例）
A1=0:00、B1=24:00、C1=1、D1=2、G1=0のときE1=8、F1=13
●休日のとき
A1=6:30、B1=18:30、C1=0、D1=1、G1=1のときE1=0、F1=11
・・・のようにE1、F1を求めたいのです。
複雑な式になるかと思いますが、よろしくお願いいたします。

回答No.1

mshr1962 · ベストアンサー

E1=IF(G1=1,0,MAX(MAX(MIN(B1*24,17.5),8.5)-MIN(MAX(A1*24,8.5),17.5)-C1,0))
F1=IF(G1=1,(B1-A1)*24-D1,MAX(MIN(B1*24,8.5)-MIN(A1*24,8.5)+MAX(B1*24,17.5)-MAX(A1*24,17.5)-D1,0))

お礼

早速の回答ありがとうございます。
書き忘れてしまったのですが、excel2007を使用しています。
mshr1962様、察していただきましてありがとうございます。
試してみます。ありがとうございました。

回答No.3

hallo-2007

以下は、アドバイスです。
勤務時間のルールは結構　IF文を多用します。
後から式を見ても良くわからなくなることもあると思います。
列の後ろの方に
　A　B　C　D　E　F　　G　　　　　H　　I　　　　J　　　　　K
　　　　　　　　　早出残業時間　午前　午後　定時後残業　休日計算
とか作業列作って
E列やF列に　IF文で　=IF(G2=0,G2+J2,K2)　＊残業の場合
とかにしておくとメンテナンスもやりやすいかと思います。
作業列が目障りであれば、非表示にしておきます。

お礼

アドバイス恐れ入ります。
見えないところにワークエリアを作ってやる手法ですね。
今回は、この手法を使用しない前提がありました。
質問の時に書いておく必要がありましたね。
大変申し訳ありませんでした。
アドバイスいただきありがとうございました。

回答No.2

DIooggooID

Ｅ１　：

=IF(G1<>1,IF(B1>=8.5/24,IF(B1>=17.5/24,17.5/24,B1),0)-IF(B1>=8.5/24,IF(A1<=8.5/24,8.5/24,A1),0)-C1/24,0)

Ｆ１　：

=IF(G1<>1,IF(A1<8.5/24,(8.5/24-A1),0)+IF(B1>=8.5/24,IF(B1>17.5/24,(B1-17.5/24),0),0)-D1/24,IF(A1<8.5/24,(8.5/24-A1),0)+IF(B1>=8.5/24,IF(B1>17.5/24,(B1-17.5/24),0),0)-D1/24+IF(B1>=8.5/24,IF(B1>=17.5/24,17.5/24,B1),0)-IF(B1>=8.5/24,IF(A1<=8.5/24,8.5/24,A1),0)-C1/24)

お礼

回答ありがとうございます。
解決できそうです。
どうもありがとうございました。