エクセルVBA 月の中で、月～土曜日まである週のうち、最終週を求めるには

発注関係のプログラムをVBAで作成しています。
月の中で、1週間で月～土のある週のうち、最終週を求めたいと考えています。

伝わりにくいのですが、例えば2024年であれば
9月：第5週まであるが、月～土まであるのは9/22の週
10月：10/20の週

といったようにして求めたいと考えています。
if文のフラグとして使う予定です。

Weekday関数なのか、Eomonth関数なのか、どれを組み合わせるとスマートな方法に
なるでしょうか。
ヒントをいただければと思います。

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

• 回答ありがとうございます。
  そうですね、その戻り値があれば、inputboxで指定した日付と比較ができます。
  それを基にして、該当日以前なら通常処理を、該当日よりも後であれば、戻り値を基準に処理をする
  といったプログラムになります。

  No.1の回答に寄せられた補足コメントです。 補足日時：2024/09/13 10:34

No.4 ベストアンサー 回答者： bonaron 回答日時： 2024/09/13 11:41

こんな感じでも。

Function LastWeek(y, m) As Date
Dim dt As Date
dt = DateSerial(y, m + 1, 1)
LastWeek = dt - Weekday(dt) - 6
End Function

【使用例】
? LastWeek(2024,9)
2024/09/22

No.6 回答者： fujillin 回答日時： 2024/09/13 12:00

こんにちは

指定月の最終土曜日を求めて、その6日前（＝日曜日）がお求めの日になると考えられます。

指定する月を 年、月で指定するとして、
Function lastWeekSet(ByRef y As Integer, ByRef m As Integer) As Date
　lastWeekSet = DateSerial(y, m + 1, 0) ' 月末日
　lastWeekSet = lastWeekSet - 6 - (Weekday(lastWeekSet) Mod 7)
End Function
のような関数を作成しておけば良いでしょう。

使い方は、上記の通りで、例えば 2024/9 なら
　dd = lastWeekSet(2024, 9)
とすれば、変数 dd には 2024/9 で、一週が全部含まれている週の日曜日である 2024/9/22 のシリアル値が返されます。

No.5 回答者： tatsumaru77 回答日時： 2024/09/13 11:57

No2です。

No4の方の回答のほうが、すっきりしてますね。
そちらを採用してください。

この回答へのお礼

こちらにて、皆様へのお礼とさせていただきます。
色々と教えていただき、ありがとうございました。

皆様からの共通なものとして、翌月初日からのweekday分と、そこから定数である土曜日のweekday分の6を引くという考え方がよくわかりました。
こちらが難しく考えていたようで、決まった法則で処理することができますね。勉強になります、ありがとうございました。

お礼日時：2024/09/14 16:21

No.3 回答者： tatsumaru77 回答日時： 2024/09/13 11:18

回答を投稿したのですが、画像審査中のため、回答が表示されていませんが、回答が表示されたら、この回答を読んでください。

Private Function getLastSunday(ByVal yyyy As Long, ByVal mm As Long) As Date
の中に
Debug.Print Lastday
の行がありますが、これは確認用の行です。
コメントアウトするか、削除するかしておいてください。

No.2 回答者： tatsumaru77 回答日時： 2024/09/13 10:49

以下は、西暦年、月が指定された時、該当する日曜日の日付を

返す関数　getLastSunday　
とその関数の呼び出し例です。
添付図のA列が西暦年、B列が月になっています。この時、西暦年と月から
該当する日曜日の日付を求め、C列に表示しています。
呼び出し側のプロシージャである月土最終週()はテストのために作成したものです。実際に使用するのは、getLastSundayになります。

getLastSundayの考え方は以下の通りです。
①指定年月の月末日を取得
②月末日から１日に向かって、以下の判定を繰り返す。
　その日が土曜日かを判定し、土曜日なら、その日付から６日を引いた日付が、求める日付となる。（繰り返し終了）
　その日が、土曜日でないなら、更にその日の前日の日付について同様に判定を行う。

------------------------------------------------
Public Sub 月土最終週()
Dim ws As Worksheet
Dim yyyy As Long
Dim mm As Long
Set ws = ActiveSheet
For wrow = 2 To 13
yyyy = ws.Cells(wrow, 1).Value
mm = ws.Cells(wrow, 2).Value
ws.Cells(wrow, 3).Value = getLastSunday(yyyy, mm)
Next
End Sub

Private Function getLastSunday(ByVal yyyy As Long, ByVal mm As Long) As Date
Dim Lastday As Date
Dim wday As Date
'月末日を求める
Lastday = DateSerial(yyyy, mm + 1, 1) - 1
Debug.Print Lastday
'月末日に最も近い土曜日の日付を探す
For wday = Lastday To Lastday - 7 Step -1
If Weekday(wday) = 7 Then
'探した土曜日の日付から６日減算する
getLastSunday = wday - 6
Exit Function
End If
Next
End Function

No.1 回答者： tatsumaru77 回答日時： 2024/09/13 10:23

西暦年と月が与えられた時、該当する日曜日の日付を返すような関数があれば良いのでしょうか。

例
年＝２０２４
月＝９の時
戻り値＝2024/9/22

年＝２０２４
月＝１０の時
戻り値＝2024/10/20