Excel2007で土日祝17時以降の勤務時間表示

質問

タイムカードの計算をしています。
土日祝の１７時以降の勤務時間のみを表示させ、最後にその時間の合計を表示させたいです。

日付と曜日のB列、C列は別シートのカレンダーから参照させており、
カレンダーシートのB４に
=IF(MONTH(A4)>$D$1,"",A4)
※A4には年月日が、D1には月が入っています。

カレンダーシートの曜日は
=IF(B4="","",CHOOSE(WEEKDAY(B4,1),"日","月","火","水","木","金","土"))
と入力しています。

また、カレンダーシートは年月を変えると祝日や曜日が変わるようにしており、
それらの曜日や日付けで条件付き書式を設定し文字色が変わるようにしています。
祝日は「祝日」シートで各年毎に「祝日１１」等と設定しています。

で、本題ですがP列の１７時以降の勤務時間を土日祝のみ表示させ、
その合計時間をP３４に表示させたいのです。

分かりにくいかもしれませんが、ご教授お願いいたします。

回答No.1

keithin · ベストアンサー

基本的にやりたいことは
＞Ｐ列の１７時以降の勤務時間を土日祝のみ表示させたい
＞合計時間をＰ３４に表示させたい

の２点ですが，これは既に出来ている
＞それらの曜日や日付けで条件付き書式を設定し文字色が変わるようにしています。

を少し応用するだけで，計算できます。

Ｐ３セルの式の基本的な考え方：
=IF(OR(３行目が土曜日である，日曜日である，祝日である）,今できているＰの式，"")

P34セルの基本的な考え方：
=SUM(P3:P33)

条件付き書式の数式は恐らく
=OR(D3="土",D3="日"，COUNTIF(祝日一覧，当該の日付のセル)=1)
などのように仕込んでるんじゃないかな？と推測しますが，そのまんま利用して
=IF(OR(D3="土",D3="日"，COUNTIF(祝日一覧，当該の日付のセル)=1),今できているＰの式，"")
などのようになります。

P列に土日祝だけ時間数の計算を出せれば，合計のセルは単純にSUMするだけで計算できます。
その際，セルの書式設定の表示形式はユーザー定義で
[h]:mm
などのように設定しておきます。

お礼

ありがとうございます！！！！！！！！！
お陰さまで出来ました！！
結果の式は以下です。

=IF(OR(C3="土",C3="日",COUNTIF(祝日１１,B3)=1),
IF(OR(H3=0,K3<=$R$3),0,
IF($R$3<=H3,K3-H3,
IF($R$3<=CEILING(J3,"0:15"),K3-CEILING(J3,"0:15"),K3-$R$3)
)),"")

上記の内、$R$3のセルは17:00とわざわざセルを用意して参照して計算しているのですが、もしかして式の中に入れたりできるのでしょうか？

ちなみに条件付き書式の色付けですが、
土曜日は、=WEEKDAY($B3)=7で青色に
日祝は、=OR(WEEKDAY($B3)=1,COUNTIF(祝日１１,$B3))で赤色にしています。
年度が変わる度に変更が必要なのが多少面倒なんですが、一年に一回の作業だからいいかな？

いやー本当に助かりました。ありがとうございました！

補足

あ、ちょっと分かりにくいですね。
H列＝出勤時間
I列＝休憩入時間
J列＝休憩終時間
K列＝退勤時間

B列＝日付
C列＝曜日です。