エクセル残業計算の関数教えてください!!

質問

EXCELで残業計算書を作成していますが、なにぶん素人なもので、独り孤軍奮闘しております。
どうしても、うまく組めず困り果てております。皆様のお知恵をお貸しください・・・

エクセル表で

A列　　　B列　　　　C列　　　　　　D列　　　　　　　　　E列
12/1　　月曜日　　残業時間　　労働時間　　　　結果（1週間の合計D列が40時　間を超えた場合は、超えた時間　
　　　　　　　　　　　　　　　　　　　（残業含む）　　　　　　　　　　　　　43.25ｈ→3.25ｈ）
12/2　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（40ｈを超えていない場合は、C列の計）をだしたいんです。
　・
12/28(日）-12/31（水）のような場合はD列計は次の月のD列頭に移行する

通常は日曜から土曜日で1週間を区切るのですが、月初めと月終わりが素直に日曜～土曜までではなく、
週の途中から始まってしまったりします。

よろしくお願いいたします

回答No.2

nayuta_lot · ベストアンサー

こんにちは
質問者さんの意図するところと違うかもしれませんがご了承ください。

一週間の労働時間が４０時間を越えていない場合
　　　　　　　　残業時間　　労働時間　　結果
11/30　日曜日　　　0.0h　　　　0.0h　　　0.0h
12/ 1　月曜日　　　1.5h　　　　9.5h　　　1.5h
12/ 2　火曜日　　　2.0h　　　 10.0h　　　3.5h
12/ 3　水曜日　　　3.0h　　　 11.0h　　　6.5h
12/ 4　木曜日
12/ 5　金曜日
12/ 6　土曜日

一週間の労働時間が４０時間を超えた場合
　　　　　　　　残業時間　　労働時間　　結果
11/30　日曜日　　　0.0h　　　　0.0h　　　6.5h
12/ 1　月曜日　　　1.5h　　　　9.5h　　　6.5h
12/ 2　火曜日　　　2.0h　　　 10.0h　　　6.5h
12/ 3　水曜日　　　3.0h　　　 11.0h　　　6.5h
12/ 4　木曜日　　　0.0h　　　　8.0h　　　6.5h
12/ 5　金曜日　　　0.0h　　　　8.0h　　　6.5h
12/ 6　土曜日　　　0.0h　　　　0.0h　　　6.5h

としたいのでしょうか？
質問の内容からだとそう受け取れますが・・

ちなみに労働時間に残業時間を含んでいるので、一週間の労働時間を
４０時間（８時間／日×５日）とした場合、超えた時間（労働時間累計　－　４０時間）
は、残業時間の累計と同じになり、残業時間がそもそも表示されている
のならば、４０時間を越える超えないに関わらず、累計を求めるだけで
よいと思いますが、どうでしょうか？

一応、上記のようにするならば
11/30（日）～12/6（土）まで７行存在するという前提でＥ２に

=IF(SUM(D$2:D$8)>40,SUM(D$2:D$8)-40,IF(SUM(D2:D$8)=0,"",SUM(C$2:C2)))

と入力して、Ｅ８までコピーします。
コピーしたらＥ２からＥ８までセルを選択してメニューの編集から
置き換えを使って、＄を""に置き換えて＄を消します。
　検索する文字列に＄
　置換後の文字列には何も入力しない

これで、一週間分の式ができますので、12/7～12/13も同じように
行を作って、１週間の式（Ｅ２～Ｅ８）をＥ９～Ｅ１５にコピーします。　以降、必要なだけ繰り返し。

単に一週間の累計だけを別の列に表示したいのなら

=SUM(C$2:C2)

とＥ２に入力して同じようにＥ８までコピー。
上記と同様に置き換えを使って$を消せば、一週間分の式ができます。

ご参考まで

お礼

すみません、回答いただきありがとうございます

他にも不具合を発見してしまったので…
参考にもう少し、悩んでみます。

大変ありがとうございました

回答No.3

hallo-2007

No1です。提示する式間違えていました。
=IF(MONTH(A1)=MONTH(A2),IF(WEEKDAY(A2)=2,C2+D2,IF(C2+D2+E1<"40:00"*1,C2+D2+E1,C2+D2+E1-"40:00")),C2+D2)
です。
40:00　以下では　日～土単位での合計、以上の場合は　合計-40:00
ひょとしたら、質問取り違えているかも知れません。
その時はスルーしてください。

お礼

ありがとうがございました。
もう少しなやんでみます

回答No.1

hallo-2007

ご希望かどうかわかりませんが
E1セルには　=C1+D1
E2セルには　=IF(MONTH(A1)=MONTH(A2),IF(WEEKDAY(A2)=2,C2+D2,C2+D2+E1),C2+D2) 下までコピィ
E列の書式　ユーザー定義　[h]:mm
で試してみてください。