VBAで月末・月初を取得する方法|DateSerialで前月・翌月も求めるモジュール化【VBA入門】

Excel実務では、営業、経理、総務部門など、売上や支払いなど、締め日などの日付の取得や変換などが多いもの。

みなさん、こんにちは。セルネッツ竹本です。
今回は、「月末・月初の求め方」をテーマに、解説をしたいと思います。

結論から言うと、VBAで月末を求めるには「DateSerial(Year(日付), Month(日付) + 1, 0)」と書きます。
月初は、日の引数を1にした「DateSerial(Year(日付), Month(日付), 1)」です。
よく使う処理なので、Functionにして共通のモジュールに置くと、どの処理からも1行で呼び出せます。

この記事では、次のことがわかります。

• DateSerial関数で月末・月初を求める考え方
• 今月・前月・翌月の月初と月末を求める式の早見表
• Function化して使い回す方法と、締め日・支払日の計算例

VBAで月末・月初を求める考え方

月末は「翌月1日の前日」として求めると、月ごとの日数を気にせずに済みます。

任意の日付が与えられたとき、当該月の月末は、何年何月何日か。

取得したいのは、それだけです。

考え方のポイントは、「翌月1日」を最初に求めることです。

それが分かれば、「その前日」が、求めたい月末です。

うるう年も、大の月、小の月、問題なく取得できます。

なーんだ、やっぱり。という方も多いと思います。

比較的、よく登場する処理は、専用のModuleを用意しておくと流用がきき、効率があがります。

月初は、もっと簡単です。
その月の「1日」なので、年と月はそのままで、日に1を指定するだけです。

DateSerial関数の基本

DateSerial関数は、年・月・日の3つの数値から日付を作るVBAの関数です。

DateSerial(年, 月, 日)

'// 例:2024年2月10日を作る
Dim 日付 As Date
日付 = DateSerial(2024, 2, 10)

引数	指定するもの	ポイント
年	100〜9999の数値	2桁で書くとPCの設定で解釈が変わるため、4桁で書く
月	数値・計算式	13は翌年1月、0は前年12月として扱われる
日	数値・計算式	0を指定すると「前月の末日」になる

月末を求められるのは、範囲を超えた値が上の単位へ繰り越される仕組みがあるからです。
日に0を指定すると「1日の前日」、つまり前月の末日になります。
12月の「月 + 1」も、自動で翌年1月として計算されます。

(参考:Microsoft Learn「DateSerial 関数」)

月末を求めるモジュール(基本形)

まずは、考え方そのままの基本形です。こんなモジュールでOKです。

Public P_Before日付 As String
Public P_戻り値 As String

'※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
Sub M_日付変換_月末の取得(P_Before日付, P_戻り値)
'※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※

 '// 翌月1日を取得
P_戻り値 = DateSerial(Year(P_Before日付), Month(P_Before日付) + 1, 1)

'// 翌月1日の前日を取得
P_戻り値 = DateSerial(Year(P_Before日付), Month(P_Before日付) + 1, 0)

Debug.Print P_戻り値

End Sub

1つ目の式は、考え方を示すための「翌月1日」です。
結果はすぐ次の式で上書きされるので、実際に使うときは2つ目の式だけで足ります。

今月・前月・翌月の月初と月末の早見表

月の引数に足し引きする数を変えるだけで、前月や翌月の月初・月末も求められます。

求めたい日	式(基準日をdとする)	d = 2024/3/15 の結果
今月の月初	DateSerial(Year(d), Month(d), 1)	2024/3/1
今月の月末	DateSerial(Year(d), Month(d) + 1, 0)	2024/3/31
前月の月初	DateSerial(Year(d), Month(d) – 1, 1)	2024/2/1
前月の月末	DateSerial(Year(d), Month(d), 0)	2024/2/29
翌月の月初	DateSerial(Year(d), Month(d) + 1, 1)	2024/4/1
翌月の月末	DateSerial(Year(d), Month(d) + 2, 0)	2024/4/30

2024年はうるう年なので、前月の月末は2月29日になります。
1月の前月(前年12月)や、12月の翌月(翌年1月)も、年をまたいで正しく計算されます。

Functionにして共通モジュールで使い回す

何度も使うなら、結果を戻り値で返すFunctionにしておくと便利です。
次のコードを標準モジュールに貼り付ければ、どのプロシージャからも呼び出せます。

'// 月末日を返す(月数:0=今月、-1=前月、1=翌月)
Public Function GetMonthEnd(ByVal 基準日 As Date, Optional ByVal 月数 As Long = 0) As Date
    GetMonthEnd = DateSerial(Year(基準日), Month(基準日) + 月数 + 1, 0)
End Function

'// 月初日を返す(月数:0=今月、-1=前月、1=翌月)
Public Function GetMonthStart(ByVal 基準日 As Date, Optional ByVal 月数 As Long = 0) As Date
    GetMonthStart = DateSerial(Year(基準日), Month(基準日) + 月数, 1)
End Function

Sub Test_月末月初()
    Debug.Print GetMonthStart(#3/15/2024#)       '2024/03/01
    Debug.Print GetMonthEnd(#3/15/2024#)         '2024/03/31
    Debug.Print GetMonthEnd(#3/15/2024#, -1)     '2024/02/29
    Debug.Print GetMonthEnd(#3/15/2024#, 1)      '2024/04/30
End Sub

#3/15/2024# は、VBAのコード内で日付を書くときの形(月/日/年)です。
標準モジュールに置いたPublic Functionは、セルの数式からも「=GetMonthEnd(A1)」のように使えます。

比べる点	Sub+Public変数(基本形)	Function
結果の受け取り方	Public変数に入った値を読む	戻り値を直接受け取る
複数の日付を扱うとき	変数が上書きされやすい	呼ぶたびに独立して計算される
セルの数式から使う	使えない	使える
向いている場面	1つの処理の中だけで使う	複数の処理・ブックで使い回す

よく使う処理を1つのモジュールに集めておく考え方は、Save As ステートメントを1Moduleに集約する方法でも紹介しています。

実務での使い方:月末判定と締め日・支払日

月末・月初の考え方を応用すると、月末かどうかの判定や、締め日・支払日の計算もできます。

その日が月末かどうかを判定する

翌日が1日なら、その日は月末です。

'// 月末ならTrueを返す
Public Function IsMonthEnd(ByVal 対象日 As Date) As Boolean
    IsMonthEnd = (Day(対象日 + 1) = 1)
End Function

締め日・支払日を求める(20日締め翌月10日払いなど)

取引先ごとに締め日と支払日が違う場合も、日を数値で渡せば同じFunctionで計算できます。
ここでは「99」を渡すと月末として扱います。31日を指定した場合も、その月の末日になります。

'// 指定した年月の「指定日」を返す(月の日数を超える日・99は月末日にする)
Private Function DayOrMonthEnd(ByVal 年 As Long, ByVal 月 As Long, ByVal 日 As Long) As Date
    Dim 月末 As Date
    月末 = DateSerial(年, 月 + 1, 0)
    If 日 >= Day(月末) Then
        DayOrMonthEnd = 月末
    Else
        DayOrMonthEnd = DateSerial(年, 月, 日)
    End If
End Function

'// 取引日から締め日を返す(20日締めなら 締め日=20、月末締めなら 締め日=99)
Public Function GetCloseDate(ByVal 取引日 As Date, ByVal 締め日 As Long) As Date
    Dim 当月の締め日 As Date
    当月の締め日 = DayOrMonthEnd(Year(取引日), Month(取引日), 締め日)
    If Day(取引日) <= Day(当月の締め日) Then
        GetCloseDate = 当月の締め日
    Else
        GetCloseDate = DayOrMonthEnd(Year(取引日), Month(取引日) + 1, 締め日)
    End If
End Function

'// 締め日から翌月の支払日を返す(10日払いなら 支払日=10、月末払いなら 支払日=99)
Public Function GetPayDate(ByVal 締め日付 As Date, ByVal 支払日 As Long) As Date
    GetPayDate = DayOrMonthEnd(Year(締め日付), Month(締め日付) + 1, 支払日)
End Function

Sub Test_締め日支払日()
    Dim 締め As Date
    締め = GetCloseDate(#1/25/2024#, 20)
    Debug.Print 締め, GetPayDate(締め, 10)       '2024/02/20  2024/03/10

    締め = GetCloseDate(#1/15/2024#, 99)
    Debug.Print 締め, GetPayDate(締め, 99)       '2024/01/31  2024/02/29
End Sub

取引日	条件	締め日	支払日
2024/1/20	20日締め翌月10日払い	2024/1/20	2024/2/10
2024/1/25	20日締め翌月10日払い	2024/2/20	2024/3/10
2024/12/25	20日締め翌月10日払い	2025/1/20	2025/2/10
2024/1/15	月末締め翌月末払い	2024/1/31	2024/2/29

請求書の支払期限を自動で入れたい場合は、請求書をエクセルで作る方法もあわせてご覧ください。
勤怠の締め日ごとの集計は、勤怠管理をエクセルで自動化する方法で解説しています。

WorksheetFunction.EoMonthとの違い

ExcelのEOMONTH関数をVBAから呼ぶこともできますが、戻り値は日付ではなく数値(シリアル値)です。

Dim 月末 As Date
月末 = CDate(WorksheetFunction.EoMonth(Date, 0))     '今月末
月末 = CDate(WorksheetFunction.EoMonth(Date, -1) + 1) '今月初(前月末の翌日)

比べる点	DateSerial	WorksheetFunction.EoMonth
種類	VBAの関数	Excelのワークシート関数
戻り値	日付	数値(CDateで日付に変換する)
月初の求め方	日に1を指定する	前月末に1を足す
Excel以外のVBA	使える(AccessやWordでも同じ)	使えない
日付でない値を渡したとき	エラー「型が一致しません」	エラー「EoMonth プロパティを取得できません」

VBAの中で使うなら、変換のいらないDateSerialをおすすめします。

月末・月初を求めるときの注意点

式は1行でも、渡す日付の形によっては思わぬ結果やエラーになります。

• 文字列の日付:「2024/2/10」は日付として扱えますが、「20240210」は「型が一致しません」のエラーになります。IsDateで確かめてからCDateで日付に変換すると安全です
• 2桁の年:DateSerialの年を2桁で書くと、何年として扱うかがPCの設定で変わります。年は4桁で指定します
• 時刻付きの日付:Nowの値などには時刻が含まれます。DateSerialの結果は0時0分なので、「=」で比べると一致しません。Dayで日だけを比べるか、Dateを使います
• 土日・祝日:支払日が休日に当たる場合の前倒し・後ろ倒しは、別に判定が必要です。曜日の判定は土日を判定してセルに色を付ける方法で解説しています
• テストする日付:月またぎ・12月・うるう年の2月で必ず確かめます。想定と違う日付が出たときは、実行時エラーの原因を突き止めるデバッグのコツの手順で値を確認します

よくある質問

Q. その月が何日まであるか(日数)を知りたいときは?

月末の日付からDayで日を取り出します。
「Day(DateSerial(2024, 2 + 1, 0))」なら、2024年2月の日数である29が返ります。

Q. 前月末をいちばん簡単に求めるには?

「DateSerial(Year(d), Month(d), 0)」です。
今月の「0日」が、前月の末日になります。1月なら前年の12月31日が返ります。

Q. マクロを使わず、ワークシートで月末を出すには?

EOMONTH関数を使います。A1の日付の月末なら「=EOMONTH(A1,0)」、月初なら「=EOMONTH(A1,-1)+1」です。
結果が数値で表示されたときは、セルの表示形式を日付にします。

まとめ|月末は「翌月の0日」、月初は「今月の1日」

実は、日付の操作は、とても頻度の高い処理のひとつでもあります。

締め日、支払日を求めたり、生年月日から日数を求めたり、いろいろあります。

今回のテーマは、「月末・月初の求め方」について、解説を行いました。

• 月末は DateSerial(Year(d), Month(d) + 1, 0)、月初は DateSerial(Year(d), Month(d), 1)
• 月の引数に足し引きする数を変えれば、前月・翌月の月初と月末も求められる
• Functionにして共通モジュールに置くと、締め日・支払日の計算にも使い回せる

本記事が、Excel実務の効率化において、ご参考お役立てになれば幸いです。

---