11月の第1月曜日っていつ?をExcelで求める方法(ゴミ収集日や祝日の計算に役立つ)

広告
yukimi
yukimi

ゴミの収集等で第3土曜日っていつ?と日付を知りたい時ありますよね。
そんな時に役立つExcelテクニックです。

下図はイメージです。

2023年1月第1月曜日の求めたもの

内部の仕組みはこのようになっています。

2023年1月第1月曜日の求めたもの②

今回は特定の月の第○□曜日を求める式の仕組みについて、以下の流れで解説していきます。

使用する関数

第○□曜日を求めるのに使用する関数はWEEKDAY関数とIF関数です。
応用編になってくるとこれに+MONTH関数を使っていきます。

【WEEK関数】

WEEKDAY関数は、日付の曜日を数字で返す関数になります。
例:日=1、月=2、火=3、水=4、木=5、金=6、土=7
※上記値は初期設定時です。

【IF関数】

IF関数は条件分岐で、この条件分岐がとても重要な役割を果たします。
式の解説の所で詳しく説明いたします。

使用する式

第1日曜日(基準の式)を計算する式は、以下の式になります。
=IF(WEEKDAY($A$1)<=1,1-WEEKDAY($A$1)+$A$1,(7+1-WEEKDAY($A$1)+$A$1))
※$A$1には日付が入っています

日付は第1~第5までの5パターン・曜日は日曜日〜土曜日までの7パターンあり、合計35パターンの式が必要になります。

第の部分と曜日の部分の変更によって変わる式について解説いたします。

今回使用しているファイルは下のボタンよりダウンロードできます。

第○の式で変わる部分

第1の式を基準に第2~第5にかけて変更する部分は最後の箇所だけです。
それぞれ最後に加算する数に7の倍数を入れます。

=IF(WEEKDAY($A$1)<=1,1-WEEKDAY($A$1)+$A$1,(7+1-WEEKDAY($A$1)+$A$1))+7
=IF(WEEKDAY($A$1)<=1,1-WEEKDAY($A$1)+$A$1,(7+1-WEEKDAY($A$1)+$A$1))+14
=IF(WEEKDAY($A$1)<=1,1-WEEKDAY($A$1)+$A$1,(7+1-WEEKDAY($A$1)+$A$1))+21
=IF(WEEKDAY($A$1)<=1,1-WEEKDAY($A$1)+$A$1,(7+1-WEEKDAY($A$1)+$A$1))+28

曜日の式で変わる部分

おさらいですが、以下が日曜日の式になります。
=IF(WEEKDAY($A$1)<=1,1-WEEKDAY($A$1)+$A$1,(7+1-WEEKDAY($A$1)+$A$1))

月〜土曜日にかけて、以下の3か所が変わります。

=IF(WEEKDAY($A$1)<=1,1-WEEKDAY($A$1)+$A$1,(7+1-WEEKDAY($A$1)+$A$1))

月:=IF(WEEKDAY($A$1)<=2,2-WEEKDAY($A$1)+$A$1,(7+2-WEEKDAY($A$1)+$A$1))
火:=IF(WEEKDAY($A$1)<=3,3-WEEKDAY($A$1)+$A$1,(7+3-WEEKDAY($A$1)+$A$1))
水:=IF(WEEKDAY($A$1)<=4,4-WEEKDAY($A$1)+$A$1,(7+4-WEEKDAY($A$1)+$A$1))
木:=IF(WEEKDAY($A$1)<=5,5-WEEKDAY($A$1)+$A$1,(7+5-WEEKDAY($A$1)+$A$1))
金:=IF(WEEKDAY($A$1)<=6,6-WEEKDAY($A$1)+$A$1,(7+6-WEEKDAY($A$1)+$A$1))
土:=IF(WEEKDAY($A$1)<=7,7-WEEKDAY($A$1)+$A$1,(7+7-WEEKDAY($A$1)+$A$1))

第△週の5パターン×曜日の7パターン=35パターンとなります。

式の解説

今回の式は一見複雑のような感じに見えますが、重要なことは2つです。

1日が何曜日なのか

曜日を起点に求めたい日付の曜日が前にあるのか後ろにあるのか

ここからはカレンダーを使って、実際に見ていきます。
2023年11月がわかりやすいので、こちらで解説いたします。

2023年11月のカレンダー

それでは、2023年11月をベースに第1土曜日と第1月曜日を算出します。

まず2023年の11月1日を知ること。
そして11月1日と曜日の位置関係を知る必要があります。

2023年11月1日は水曜日になります。
続いて位置関係は土曜日は11月1日から数えて3日後。
月曜日は2日前になります。

求めたい曜日の位置関係

これを数式にすると

月曜日:2-WEEKDAY($A$1)+$A$1

土曜日:7-WEEKDAY($A$1)+$A$1

で求めることができます。

水曜日のWEEKDAY関数で求めた値は「4」となります。
これを公式に当てはめると

月曜日:2-WEEKDAY($A$1)+$A$1 =2-4+2023/11/1
=2023/10/30 (2日前)

土曜日:7-WEEKDAY($A$1)+$A$1 =7-4+2023/11/1
=2023/11/4 (3日後)

土曜日は第1を求めることができましたが、月曜日は先週の月曜日を算出しています。
第1月曜日にするためには1週間後の7日を足してあげる必要があります。

7日足してあげる式がこちらです。

7+2-WEEKDAY($A$1)+$A$1 =7+2-4+2023/11/1
2023/11/6(5日後)

となります。

ここでIF文の条件分岐使って、計算方法を変えてあげます。
1日の基準日よりも前なら、7を加算する。

条件分岐

その式がこちらです。
=IF(WEEKDAY($A$1)<=Weekdayの曜日

これが、第△□曜日を求める式になります。

応用編(第5で次の月となった場合は非表示)

ここまではあってもなくても良いものですが、一月は最低28日、最高31日です。
曜日の数は7あるので、翌月が表示されてしまうケースがあります。

応用編は翌月は対象外にする方法です。

=IF(MONTH(IF(WEEKDAY($A$1)<=2,2-WEEKDAY($A$1)+$A$1,(7+2-WEEKDAY($A$1)+$A$1))+28)=MONTH(A1)+1,“”,IF(WEEKDAY($A$1)<=2,2-WEEKDAY($A$1)+$A$1,(7+2-WEEKDAY($A$1)+$A$1))+28)

長い式ではありますが、注目すべき所は上記下線部だけです。
基準日の月だけを抜き出し、それが+1した月であれば非表示にしています。

まとめ

いかがでしたでしょうか。

ゴミ出しの曜日で第3土曜日っていつだよ!って計算するのたいへんですが、Excelで簡単に算出できますので、ぜひ使ってみてください。

コメント