Excel VBAで月末日を取得する方法をお探しですね。
広告
Excel VBAで月末日と曜日を自動取得する方法【初心者向け】
Excel VBAで日付を扱うとき、「この日付の月末っていつだっけ?」「この日は何曜日?」って知りたくなることがよくありますよね。
請求書の締め日、勤務表、月次レポート、カレンダー作成など、日付を手入力するとミスが起きやすいですし、月によって日数が違うのも面倒です。
この記事では、Excel VBAで指定した日付の「月末日」や「曜日」を自動で取得する方法を、初心者にも分かりやすく解説します。
VBAで日付を扱う基本
まずは、VBAで日付をどう扱うかを理解しておきましょう。
Excelでは日付を「2026/1/31」のように表示していますが、実は内部では「シリアル値」という連続した数値で管理しています。
だから、日付に「+1」すれば翌日、「-1」すれば前日になるんです。
VBAで日付を扱うときは、変数の型に「Date」を使うのが基本です。
Date型にしておくと、文字列じゃなくて日付として安全に処理できるので、月や年を取り出したり、日付同士を比較したりしやすくなります。
たとえば、セルA1に入力された日付をもとに処理したい場合は、こんな感じで変数に入れます。
“`vb
Sub GetDateSample()
Dim targetDate As Date
targetDate = Range(“A1”).Value
MsgBox targetDate
End Sub
“`
ただし、セルに日付じゃない文字列が入っているとエラーになっちゃいます。
実務で使うマクロでは、いきなりDate型に入れるんじゃなくて、IsDate関数で「これって日付?」って確認してから処理すると安心です。
“`vb
Sub CheckDateSample()
If IsDate(Range(“A1”).Value) Then
MsgBox “日付として処理できます”
Else
MsgBox “A1には正しい日付を入力してください”
End If
End Sub
“`
セルから取得する以外に、コード内で直接日付を指定する方法もあります。
たとえば「2026年2月10日」を対象にするなら、DateSerial関数を使って「DateSerial(2026, 2, 10)」と書けます。
DateSerialは年・月・日を指定して日付を作る関数で、VBAの日付処理ではめちゃくちゃよく使います。
文字列で「”2026/2/10″」と書くより、日付としての意図がはっきりするし、環境による表記のズレの影響も受けにくいのがメリットです。
月末日を自動で取得する方法
VBAで指定した日付の月末日を取得する代表的な方法は、DateSerial関数を使う方法です。
考え方はシンプルで、「翌月の1日」を作って、そこから1日引けば当月の月末日になります。
たとえば、2026年2月の月末日を知りたい場合、2026年3月1日から1日引けば2026年2月28日になりますよね。
うるう年なら、ExcelとVBAが自動的に計算してくれるので、2024年2月なら自動的に2月29日になります。
基本のコードはこんな感じです。
セルA1に指定日を入力して、セルB1にその月の月末日を表示する例です。
“`vb
Sub GetEndOfMonth()
Dim targetDate As Date
Dim endOfMonth As Date
If Not IsDate(Range(“A1”).Value) Then
MsgBox “A1に正しい日付を入力してください”
Exit Sub
End If
targetDate = Range(“A1”).Value
endOfMonth = DateSerial(Year(targetDate), Month(targetDate) + 1, 0)
Range(“B1”).Value = endOfMonth
Range(“B1”).NumberFormatLocal = “yyyy/m/d”
End Sub
“`
ポイントは、DateSerialの3番目の引数に「0」を指定しているところです。
DateSerialでは「日」に0を指定すると、指定した月の前月末日を返してくれます。
だから「Month(targetDate) + 1」で翌月を指定して、日を0にすることで、結果的に指定日の月の末日が取得できるんです。
この書き方なら短くて済むし、2月・30日までの月・31日までの月を個別に判定する必要がありません。
Excelのワークシート関数にはEOMONTH関数がありますが、DateSerialを使えばVBAだけで完結します。
マクロとして配布する場合や、関数の互換性を気にせず処理したい場合は、DateSerialを使う方法を覚えておくと便利です。
“`vb
Sub GetNextMonthEnd()
Dim targetDate As Date
Dim nextMonthEnd As Date
targetDate = Range(“A1”).Value
nextMonthEnd = DateSerial(Year(targetDate), Month(targetDate) + 2, 0)
Range(“B2”).Value = nextMonthEnd
End Sub
“`
このように、月末日の取得は応用もしやすいです。
翌月末なら「Month(targetDate) + 2, 0」、前月末なら「Month(targetDate), 0」と指定します。
請求書の支払期限を「翌月末」にしたい場合や、月次集計の対象期間を自動で決めたい場合にも使えます。
月末日を手入力していると、2月やうるう年でミスが起こりやすいので、VBAで自動化する効果は大きいですよ。
曜日を自動で取得する方法
指定した日付の曜日をVBAで取得する方法には、大きく分けて2つあります。
「Format関数で曜日名を表示する方法」と「Weekday関数で曜日番号を取得する方法」です。
曜日をそのまま「月曜日」「火曜日」のように表示したい場合はFormat関数が簡単です。
一方で、土日判定や定休日判定のように条件分岐に使いたい場合は、Weekday関数で数値として取得する方法が向いています。
まず、セルA1の日付から曜日名を取得して、セルC1に表示する例を見てみましょう。
“`vb
Sub GetWeekdayName()
Dim targetDate As Date
If Not IsDate(Range(“A1”).Value) Then
MsgBox “A1に正しい日付を入力してください”
Exit Sub
End If
targetDate = Range(“A1”).Value
Range(“C1”).Value = Format(targetDate, “aaaa”)
End Sub
“`
Format関数で「aaaa」を指定すると、「月曜日」「火曜日」のように曜日を表示できます。
「aaa」を指定すると「月」「火」のような短い表記になります。
日付と曜日を一緒に表示したい場合は、「yyyy/m/d(aaa)」のように指定できます。
“`vb
Sub GetDateWithWeekday()
Dim targetDate As Date
targetDate = Range(“A1”).Value
Range(“C2”).Value = Format(targetDate, “yyyy/m/d(aaa)”)
End Sub
“`
一方、曜日によって処理を変えたい場合はWeekday関数を使います。
Weekday関数は、指定した日付が何曜日かを数値で返す関数です。
標準では日曜日が1、月曜日が2、土曜日が7になります。
ただし、第2引数を指定することで、月曜日を1、日曜日を7として扱うこともできます。
業務では「土日なら休み」「平日だけ処理する」といった判定が多いので、第2引数に「vbMonday」を指定して月曜始まりにすると扱いやすいです。
“`vb
Sub JudgeWeekend()
Dim targetDate As Date
Dim dayNo As Integer
targetDate = Range(“A1”).Value
dayNo = Weekday(targetDate, vbMonday)
If dayNo >= 6 Then
Range(“D1”).Value = “土日”
Else
Range(“D1”).Value = “平日”
End If
End Sub
“`
この例では、vbMondayを指定しているので、月曜日が1、火曜日が2、土曜日が6、日曜日が7になります。
その結果、「dayNo >= 6」と書くだけで土日を判定できます。
曜日を表示するだけならFormat関数、曜日を条件分岐に使うならWeekday関数、と使い分けるとコードが読みやすくなります。
月末日と曜日を組み合わせた実務例
月末日と曜日の取得は、それぞれ単独でも便利ですが、実務では組み合わせて使う場面が多いです。
たとえば、指定した年月のカレンダーを作成したり、月末日が土日なら前営業日に調整したり、請求締め日と曜日を一覧に表示したり。
ここでは、セルA1に入力された日付をもとに、月初から月末までの日付と曜日を一覧表示するサンプルを紹介します。
“`vb
Sub CreateMonthlyDateList()
Dim targetDate As Date
Dim startDate As Date
Dim endDate As Date
Dim currentDate As Date
Dim rowNo As Long
If Not IsDate(Range(“A1”).Value) Then
MsgBox “A1に正しい日付を入力してください”
Exit Sub
End If
targetDate = Range(“A1”).Value
startDate = DateSerial(Year(targetDate), Month(targetDate), 1)
endDate = DateSerial(Year(targetDate), Month(targetDate) + 1, 0)
Range(“A3:B100”).ClearContents
rowNo = 3
For currentDate = startDate To endDate
Cells(rowNo, 1).Value = currentDate
Cells(rowNo, 1).NumberFormatLocal = “yyyy/m/d”
Cells(rowNo, 2).Value = Format(currentDate, “aaaa”)
rowNo = rowNo + 1
Next currentDate
End Sub
“`
このコードでは、まず指定日から月初日と月末日を作成しています。
その後、For文で月初から月末まで1日ずつ処理して、A列に日付、B列に曜日を出力します。
2月なら28日または29日で止まるし、4月なら30日、1月なら31日まで自動で出力されるので、月ごとの日数を意識する必要がありません。
さらに実務では、月末日が土日だった場合に前の金曜日を期限にしたいケースがあります。
その場合は、月末日の曜日番号を取得して、土曜日なら1日前、日曜日なら2日前に調整します。
次の例では、指定日の月末日を求めたうえで、土日なら前営業日に変更してセルB1へ表示します。
“`vb
Sub GetBusinessMonthEnd()
Dim targetDate As Date
Dim endOfMonth As Date
Dim businessEnd As Date
If Not IsDate(Range(“A1”).Value) Then
MsgBox “A1に正しい日付を入力してください”
Exit Sub
End If
targetDate = Range(“A1”).Value
endOfMonth = DateSerial(Year(targetDate), Month(targetDate) + 1, 0)
businessEnd = endOfMonth
Select Case Weekday(endOfMonth, vbMonday)
Case 6
businessEnd = endOfMonth – 1
Case 7
businessEnd = endOfMonth – 2
End Select
Range(“B1”).Value = businessEnd
Range(“C1”).Value = Format(businessEnd, “aaaa”)
End Sub
“`
このサンプルでは祝日は考慮していませんが、土日を避けるだけでも請求書や社内締め処理では役立ちます。
祝日も含めて営業日判定をしたい場合は、別シートに祝日一覧を用意して、その日付が祝日リストに含まれるかを確認する処理を追加すると、より実務向けになります。
最初から複雑なコードにするより、まずは月末日と曜日を正しく取得して、その後に祝日や会社独自の休日ルールを追加していくと理解しやすいですよ。
まとめ
Excel VBAで指定した日付の「月末日」や「曜日」を自動で取得する方法は、DateSerial、Format、Weekdayの3つを押さえると効率よく実装できます。
– **月末日**はDateSerialで「翌月1日の前日」を求める
– **曜日表示**はFormatで整える
– **曜日判定**はWeekdayで数値化する
という役割分担で考えると迷いにくくなります。
これらを組み合わせれば、日付入力のミスを減らして、カレンダー作成、締め日管理、勤務表作成などの作業を安定して自動化できます。
ぜひ試してみてください!
広告
