暦で第一第三の土曜日を 休日出勤 と表示するには

いつもお世話になります。
OS Win10 エクセル2016　です。

第二、第四の土曜の「休日出勤」は下記の　F2　の数式で求められるのですが

第一、第三の土曜の「休日出勤」を求めるのにはどのように変更すればいいのでしょうか。
ご指導いただけませんでしょうか。

F2 =DATE(A1,C1,1)
G2 =IF(OR(AND(MOD(CEILING(DAY(F2),7),14)=0,WEEKDAY(F2)=7),WEEKDAY(F2)=1,H2=1),"休日出勤","営業")
H2 =IF(ISNA(VLOOKUP(F2,祝,4,FALSE)),"",VLOOKUP(F2,祝,4,FALSE))

インターネット等で　MOD CELLING　を調べたりあの手この手で修正したりしました。
旨くゆきません。
この関数もインターネットから得た情報です。

※　参照図は一部の行は　非表示　にしています。

質問者からの補足コメント

• 

  早速ご回答をいただきありがとうございます。
  
  私の説明不足で済みません。
  
  試しましたが第一と第二に「休日出勤」しか表示されません。
  
  私のお願いは　G列　の様に土日及び祝日の休みは　「休日出勤」　で
  それ以外は　「営業」　と表示させたいのです。
  ただし　G列　の土曜日は第二、第四の日ですが。
  
  再度確認させてください。
  
  第一、第三の土曜日+日曜日+祝日　"休日出勤"
  それ以外は　"営業"　に表示する関数です。
  
  ご苦労ですが再度ご指導お願いできませんか。
  
  ※H列の1は祝日を表しています。

  No.1の回答に寄せられた補足コメントです。 補足日時：2018/04/30 11:07

• 

  折角ご回答いただいて申し訳ありませんが月度をまたぎたいのです。
  20日〆等からまたぎますので。
  よろしくお願いします。

  No.2の回答に寄せられた補足コメントです。 補足日時：2018/04/30 11:10

• ご指導をありがとうございます
  早速試しました。
  3月4月と5月6月にして試し
  
  ↓この数式は　問題あり　と警告が出ます
  =IF(OR(AND(MOD(CEILING(DAY(F2),7),28),14,WEEKDAY(F2)=7),WEEKDAY(F2)=1,H2=1),"休日出勤","営業")
  ↓この数式は　2週目に　「休日出勤」　になります
  =IF(OR(AND(MOD(CEILING(DAY(F2),7),28),14)<>0,WEEKDAY(F2)=7),WEEKDAY(F2)=1,H2=1),"休日出勤","営業")

  No.3の回答に寄せられた補足コメントです。 補足日時：2018/04/30 15:04

• 

  3月は5週あれますが、3月31日がだめでした。

  No.4の回答に寄せられた補足コメントです。 補足日時：2018/04/30 19:26

• 

  いつもお世話になります。
  試しましたが3月は土曜が5週あり、「営業」と表示されました。
  すごくうれしいです。
  余計なことですが参考に見てください。　自画自賛です。(笑い)
  折角皆様にご指導いただきながら恐縮ですが、
  苦しんだ挙句私なりに考えたもので。
  
  何かアドバイスをいただければ幸いです。
  G2 =IF(N2=1,"休日出勤","")
  I2 =IF(J2=1,COUNTIF($J$2:J2,J2),"")
  J2 =WEEKDAY(F2,16)
  K2 =IF(I2<>"",IF(MOD(I2,2)=0,"",1))
  L2 =IF($J2=2,WEEKDAY($F2,1),"")
  M2 =IF(ISNA(VLOOKUP(F2,祝,4,FALSE)),"",VLOOKUP(F2,祝,4,FALSE))
  N2 =IF(SUM(K2:M2)>=1,1,"")

  
  No.6の回答に寄せられた補足コメントです。 補足日時：2018/04/30 19:45

• 

  No.6さんに
  かんちがいしていました。
  
  3月31日はやはりだめでした。
  5週目はいずれの方式も営業とでます。
  
  ごめんなさい
  お許しのほど
  
  小生の考えたものは　おっけい　です。

    補足日時：2018/04/30 19:52

No.8 ベストアンサー 回答者： tom04 回答日時： 2018/04/30 20:26

No.6です。

＞3月31日はやはりだめでした。
＞5週目はいずれの方式も営業とでます。

ん？
タイトルは第一・第三土曜となっていますが、
結局第一・第三土曜だけでなく、奇数土曜はすべて「休日出勤」としたい訳ですけね？
それではOR関数の条件を一つ加えるだけでいけるはずです。
G2セルの数式を
=IF(OR(WEEKDAY(F2)=1,COUNTIF(祝,F2),F2=EOMONTH(F2,-1)-WEEKDAY(EOMONTH(F2,-1)-5,3)+7,F2=EOMONTH(F2,-1)-WEEKDAY(EOMONTH(F2,-1)-5,3)+21,F2=EOMONTH(F2,-1)-WEEKDAY(EOMONTH(F2,-1)-5,3)+35),"休日出勤","営業")

としてみてください。

※　数式の細かい説明は割愛しますが、やり方としては
=EOMONTH(F2,-1)-WEEKDAY(EOMONTH(F2,-1)-5,3)

でF列に表示されている日付の前月最終土曜が求められますので、
その日付に7・21・35をプラスしたのが第一・第三・第五土曜になります。
F列シリアル値がそれに当てはまれば「休日出勤」のOR関数がTRUEとなります。

当方がお示しした数式は作業用の列は不要です。m(_ _)m

この回答へのお礼

ありがとうございました。
うまくできてホッとしています。

私の方は次の月度ではだめでした。
ごめんなさいです。

お礼日時：2018/04/30 20:43

No.7 回答者： mike_g 回答日時： 2018/04/30 20:20

》 第一、第三の土曜の「休日出勤」を求めるのには

》 どのように変更すればいいのでしょうか。
参考までに、第1、2、3、4、5 の土曜日を求める式を示しておきます。

C2: =DATE($A2,C$1,$B2*7-WEEKDAY(DATE($A2,C$1,-5),3))

A列:年
1行目:月
B列:第ｎ曜日の数値ｎ

式中の「-5」の「5」は Q列に示す曜日指数です。

ちなみに、[条件付き書式]で空欄に見せているセルは第5土曜が存在しないことを指しています。

【独白】数時間前に同じ内容で投稿したつもりでいたけど見当たらないので、また投稿した次第です。

No.6 回答者： tom04 回答日時： 2018/04/30 17:57

こんばんは！

第一・第三土曜日・日曜日・祝　の場合は「休日出勤」とし、
それ以外は「営業」になれば良いのですね？

G2セルに
=IF(OR(WEEKDAY(F2)=1,COUNTIF(祝,F2),F2=EOMONTH(F2,-1)-WEEKDAY(EOMONTH(F2,-1)-5,3)+7,F2=EOMONTH(F2,-1)-WEEKDAY(EOMONTH(F2,-1)-5,3)+21),"休日出勤","営業")

という数式を入れフィルハンドルで下へコピー！
これでどうでしょうか？m(_ _)m

No.5 回答者： ママチャリ 回答日時： 2018/04/30 16:59

「F2の日を7の倍数に切り上げた数」と28のうち、小さい方を14で割った余りがゼロでない場合…という意味の式なのですが、chabi

んが入力した式には、「小さい方」が抜けていますよ！

No.4 回答者： ArranMALT 回答日時： 2018/04/30 16:30

№2を修正しました。

G2に貼り付けて、コピーしてください。

=IF(OR($F2=DATE($a$1,$c$1,1)+(7-WEEKDAY(DATE($a$1,$c$1,1),1)),$F2=DATE($a$1,$c$1,1)+(7-WEEKDAY(DATE($a$1,$c$1,1),1)+14),$F2=DATE($a$1,$c$1+1,1)+(7-WEEKDAY(DATE($a$1,$c$1+1,1),1)),$F2=DATE($a$1,$c$1+1,1)+(7-WEEKDAY(DATE($a$1,$c$1+1,1),1)+14),WEEKDAY($F2)=1,$H2=1),"休日出勤","営業")

これで、２か月分対応できます。
３ヶ月以上を対応させたい場合は、全体的に修正しないといけませんね。

No.3 回答者： ママチャリ 回答日時： 2018/04/30 13:35

次の部分を書き換えれば、「第二第四土曜日」が「それ以外の土曜日」になります。

MOD(CEILING(DAY(F2),7),14)=0
↓
MOD(CEILING(DAY(F2),7),14)<>0

しかし、今年の３月みたいに第五土曜日がある場合は、それも休日出勤になってしまうので、第五土曜日を営業とするために、もうひと捻り加えます。

MOD(MIN(CEILING(DAY(F2),7),28),14)<>0

No.2 回答者： ArranMALT 回答日時： 2018/04/30 11:05

１ヶ月分（画像の５月分）だけであれば、こんな感じでしょうか。

G2に貼り付けて、コピーしてください。
=IF(OR($F2=$F$2+(7-WEEKDAY($F$2,1)),$F2=$F$2+(7-WEEKDAY($F$2,1)+14),WEEKDAY($F2)=1,$H2=1),"休日出勤","営業")

ただ月が変わると対応していません。画像の6月分は別シートに作ってください。

１シートで完結させるには、月の判定も必要ですね。

No.1 回答者： moon-and-star 回答日時： 2018/04/30 10:17

単純にカレンダーがA列にあり、B列に第一、第三土曜を「休日出勤」と表示させたいのであれば、B1に以下、後は下にドラッグ

=IF(AND(OR(INT((DAY(A1)-1)/7)=0,INT((DAY(A1)-1)/7)=2),WEEKDAY(A1)=7),"休日出勤","")
　　　
見当違いでしたらスルーして下さい。