excelで第二月曜日を返す、ただし祝日の場合はその次の日

excelで、A1セルに入力した日付（例：12/1）と同月の第二月曜日（例：12/11）をB1に表示させたいです。
ただしそれが祝日だった場合は、自動でその次の日に変わるようにしたいです。
関数をいろいろ組み合わせればできるのでしょうか？
宜しくお願いいたします。

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

• 忘れていました、祝日については別シートで2020年までの一覧を作成してあります。
  本文中の例は2017年を想定しています。
  足りなくてすみません。

    補足日時：2017/10/26 13:28

• なお祝日のシートは
  sheet2のA1:B89に、A列に日付・B列に何の日かが入っています。

    補足日時：2017/10/26 13:34

No.2 ベストアンサー 回答者： tom04 回答日時： 2017/10/26 14:35

こんにちは！

A列にはシリアル値が入っているとします。

B1セルに
=WORKDAY(EOMONTH(A1,-1)-WEEKDAY(EOMONTH(A1,-1)-2)+13,1,Sheet2!$A$1:$B$89)

としてみてはどうでしょうか？m(_ _)m

この回答へのお礼

EOMONTH関数を初めて知りました。
シンプルで分かりやすい関数を作っていただき、とても助かりました。
ありがとうございました。

お礼日時：2017/10/27 09:15

No.8 回答者： tom04 回答日時： 2017/10/26 18:19

No.2・7です。

たびたびごめんなさい。
No.7の説明で間違いがありました。
WORKDAY関数を使っていますので、
土・日は表示できませんね。

※　参考として　※
前月最終○曜日を求める数式で不具合が生じるコトがありました。
=EOMONTH(A1,-1)-WEEKDAY(EOMONTH(A1,-1)-0,3)

で前月最終月曜が求められます。月曜の場合の「-0」をあえて表示しています。
-1　→　火曜
-2　→　水曜
-3　→　木曜
-4　→　金曜
-5　→　土曜
-6　→　日曜

とすればそれぞれの最終○曜日が求められます。

何度も失礼しました。m(_ _)m

No.7 回答者： tom04 回答日時： 2017/10/26 16:41

No.6のmike_gさんへ

※　mimowさん、ごめんなさい。この場を借りて数式の説明を少しだけ・・・

基準日を前月の最終月曜にしています。
=EOMONTH(A1,-1)-WEEKDAY(EOMONTH(A1,-1)-2)

これで前月最終月曜のシリアル値になります。
（-2　を　-1　にすれば「日曜」　-3　で「火曜」・・・　-7　で「土曜」になります。）

仮に第二月曜を求めたい場合は前月最終月曜+14になります。

ただ、今回はWORKDAY関数を使って第二月曜が祝日の場合の処理が必要なので、
WORKDAY関数の「開始日」をその1日前とし、日数を1日としていますので、
=WORKDAY(EOMONTH(A1,-1)-WEEKDAY(EOMONTH(A1,-1)-2)+14-1,1)
（祭日の部分は割愛しています）

となり、→　開始日＝前月最終月曜+14-1　になります。
これで数式内の13の意味が分かってもらえると思います。

第一・第三月曜を求めたい場合、この部分の　プラス・マイナス7　の調整で可能になります。
第一月曜　→　+6
第三月曜　→　+20
第四月曜　→　+27
のようにするだけです。

どうも長々と失礼しました。m(_ _)m

No.6 回答者： mike_g 回答日時： 2017/10/26 16:10

この機会に[No.2]の tom04さんに、

式も短くて、お見事です。
参考までに、今回の質問者の「第二月曜日」を「第一月曜日」あるいは「第三月曜日」に変更するとしたら、提示された式の何処をどのように変更したらよろしいのでしょうか？

mimowさん、横槍を入れて申し訳ありません。

No.5 回答者： t_fumiaki 回答日時： 2017/10/26 15:06

No.4は間違い

以下をB1へ入れれば動きます(動作確認済）

=IF(COUNTIF(Sheet2!A1:A89,DATE(YEAR($A$1),MONTH($A$1),1)+VALUE(
MID("1065432",WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),1),1),1))+7),DATE(YEAR($A$1),MONTH($A$1),1)+VALUE(
MID("1065432",WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),1),1),1))+8,DATE(YEAR($A$1),MONTH($A$1),1)+VALUE(
MID("1065432",WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),1),1),1))+7)

この回答へのお礼

すごく細かくてすごいですね！
ご親切にありがとうございました。

お礼日時：2017/10/27 09:16

No.4 回答者： t_fumiaki 回答日時： 2017/10/26 15:04

なら、B1に以下を入れる

=IF(COUNTIF(Sheet2!A1:A89,DATE(YEAR($A$1),MONTH($A$1),1)+VALUE(
MID("1065432",WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),1),1),1))+7),DATE(YEAR($A$1),MONTH($A$1),1)+VALUE(
MID("1065432",WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),1),1),1))+7,DATE(YEAR($A$1),MONTH($A$1),1)+VALUE(
MID("1065432",WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),1),1),1))+7)

No.3 回答者： tom04 回答日時： 2017/10/26 14:43

No.2です。

前回書き忘れましたが、
Sheet2の「祝日一覧？」の範囲内に文字列が含まれているとエラーになります。

＞sheet2のA1:B89に、A列に日付・B列に何の日かが入っています。
というコトなので、
前回のWORKDAY関数の「祭日」の欄の
＞Sheet2!$A$1:$B$89
を
＞Sheet2!$A$1:$A$89

のようにシリアル値の範囲だけにしてください。m(_ _)m

No.1 回答者： t_fumiaki 回答日時： 2017/10/26 12:59

年が解らないと計算出来ません。

A1を2017/12/1の様にYYYY/MM/DDにして貰い、B1の書式もYYYY/MM/DDにして貰えば可能です。

B1に以下を入れます。
=DATE(YEAR($A$1),MONTH($A$1),1)+VALUE(
MID("1065432",WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),1),1),1))+7

また、祝日は年によって日付が前後するものがある為、一般論では出来ません。