エクセル出退勤表で時間外と深夜時間外の関数

エクセルで出退勤表を作りたいと思っています。
所定労働時間は10:00～18:30で休憩1時間入り、7:30です。
18:30～22:00までは時間外労働時間、22:00～翌5:00までは深夜時間外です。例えば出勤時間10:00、退勤時間23:00とすると、時間外は3:30で深夜時間外は1:00です。
セルの縦列には
A：日付（例3/11で表示）
B:曜日
C：出勤時間（例10:00で表示）
D：退勤時間（例23:30で表示）
E：所定労働時間（7:30で固定で休憩の1時間を含んでいない）
F：時間外労働時間（例3:00で表示）
G：深夜時間外（例2:30で表示）

Q1:カレンダーの暦通り、曜日をBセルに自動表示させる方法はありますか？
Q2:Fセルの18:30～22:00までの時間外労働時間を計算する関数を教えて下さい。Q3:Gセルの22:00～翌5:00までの深夜残業時間を計算する関数を教えて下さい。
Q4：出勤時間と退勤時間を入力すれば時間外と深夜時間外が表示されるようにしたいのですが可能ですか？
よろしくお願いします。

No.1 ベストアンサー 回答者： mshr1962 回答日時： 2007/03/11 15:48

>Q1:カレンダーの暦通り、曜日をBセルに自動表示させる方法はありますか？

B2=TEXT(A2,"aaa")

>Q2:Fセルの18:30～22:00までの時間外労働時間を計算する関数を教えて下さい。
F2=IF(OR(C2="",D2=""),"",MAX(MIN(D2+(D2<C2),22/24)-C2-17/48,0))

>Q3:Gセルの22:00～翌5:00までの深夜残業時間を計算する関数を教えて下さい。
G2=IF(OR(C2="",D2=""),"",MAX(D2+(D2<C2),22/24)-22/24)

※D2+(D2<C2) C2=10:00で、D2=5:00の場合にD2=27:00とします。
※22/24="22:00"=22時のこと
※17/48=8.5/24="08:30"=８時間半（休憩時間を含む所定労働時間）のこと

この回答への補足

ご回答ありがとうございます。早速エクセル作ってみました。こんな難しい関数は全く分かりませんでしたので、本当に助かりました。ありがとうございました。大変恐縮なんですが、教えていただいた関数で平日は問題なく作業することが出来るのですが、所定労働時間の無い休日出勤は教えて頂いた関数は適応できないようです。このような場合はどうしたらよいでしょうか？1：休日は所定労働時間の7：30が無い。2：22：00以降は平日同様に深夜時間外です。　　　休日に使える関数を教えていただければ光栄です。よろしくお願いします。

補足日時：2007/03/12 00:31

No.2 回答者： kwang0205 回答日時： 2007/03/13 02:41

>1：休日は所定労働時間の7：30が無い。

E2=IF(OR(B2="土",B2="日"),"",15/48)
※Ｂ列が"土"か"日"なら""、そうでなければ15/48=7.5/24="7:30"を表示

祝日や年末年始の休みは、さすがに式では求められないので、
"祝"や"休"をＢ列に手動で入れていただければ、
E2=IF(OR(B2="土",B2="日",B2="祝",B2="休"),"",15/48)
を使ってみてください。


>2：22：00以降は平日同様に深夜時間外です。
F2=IF(OR(C2="",D2=""),"",MAX(MIN(D2+(D2<C2),22/24)-C2-17/48,0)+IF(OR(B2="土",B2="日"),15/48,0))

mshr1962さんと違うのは、最後に「+IF(OR(B2="土",B2="日"),15/48,0)」を入れただけです。
1.と非常に似ていますが、土日であれば、所定労働時間に15/48=7.5/24="7:30"を加え、
土日以外なら0を加えるという式です。
1.と同様に"祝"や"休"をＢ列に手動で入れていただければ、
F2=IF(OR(C2="",D2=""),"",MAX(MIN(D2+(D2<C2),22/24)-C2-17/48,0)+IF(OR(B2="土",B2="日",B2="祝",B2="休"),15/48,0))
を使ってみてください。