こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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

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

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

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

投稿日時 - 2011-01-09 16:54:11

QNo.6436015

困ってます

質問者が選んだベストアンサー

基本的にやりたいことは
>P列の17時以降の勤務時間を土日祝のみ表示させたい
>合計時間をP34に表示させたい

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

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

P3セルの式の基本的な考え方:
=IF(OR(3行目が土曜日である,日曜日である,祝日である),今できているPの式,"")

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


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

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

投稿日時 - 2011-01-09 17:42:58

補足

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

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

投稿日時 - 2011-01-09 23:10:55

お礼

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

=IF(OR(C3="土",C3="日",COUNTIF(祝日11,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(祝日11,$B3))で赤色にしています。
年度が変わる度に変更が必要なのが多少面倒なんですが、一年に一回の作業だからいいかな?

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

投稿日時 - 2011-01-09 23:04:26

ANo.1

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(1)

あなたにオススメの質問