Excelでの勤務表作成 *夜勤がある場合の計算式

質問

Excelで勤務表を作成しているのですが
関数の作成に困っています。

私の職場には日勤（9:30～18:00）と夜勤があります
夜勤は　宿直（17:00～24:00）、宿明け（0:00～10:00）
の時間帯です。

セルA　開始時間
セルB　終了時間
セルC　休憩時間
セルD　稼働時間
セルE　所定時間
セルF　時間外残業

セルDの稼働時間を求める関数に
=IF(OR(A1="",B1=""),0,B1-A1-C1)

セルEの所定時間の関数を
=IF(D1>$N$44,$N$44,D1)　　としました。*N44には7:30を投入

この場合は日勤の場合の勤務時間は正しく反映されますが
夜勤（宿直、宿明け）の勤務時間を投入した場合
時間外残業が正しく反映されません。

所定時間を日勤の場合は7:30（7時間30分）
宿直の場合は6:00（6時間）
宿明けの場合は9:00（9時間）
と設定すればうまくいくと思うのですが
どのように設定したらよいでしょうか？

回答No.1

mshr1962 · ベストアンサー

開始時間で判定するなら
E1=MIN(D1,LOOKUP(A1*24,{0,9.5,17},{9,7.5,6})/24)

上記で開始時間の範囲で
00:00～09:29→宿明　9時間
09:30～16:59→日勤　7時間30分
17:00～23:59→宿直　6時間
が判定値になります。

お礼

mshr1962さん

とても参考になりました。
ありがとう御座います。