【Excel VBA】簡単テク！日付から月末日を求める3つのメソッド

2024年9月11日2024年10月21日

当ページのリンクには広告が含まれています。

本記事ではExcel VBAで日付を使った月末日の求め方を3つ解説していきます。

Excelで作業をしているとこんな場面で月末日を求める事が多くありませんか？

請求書作成や給与計算
月次の報告書作成
プロジェクト進捗の確認

鳩野

言われてみると確かにそうかも。。。

しかし毎回手作業で月末日を確認するのは手間がかかる上、毎月日付が異なるためミスも発生しやすくなりがちです。

悠

そんな時に役立つのがExcel VBAを使った月末日の求め方です。

本記事の方法を使えば日付から自動で月末日を計算し、作業時間の短縮やミスの軽減へと繋げる事ができます。

この記事で分かること

Excel VBAで日付から月末日を求められる方法が分かり、作業時間短縮やミスの軽減に繋げる事ができる。

本記事はWindows11 23H2 、Microsoft 365の環境で解説を行います。

日付から月末日を求める3つのメソッド

今回はExcel VBAで関数を使った２つの月末日の求め方と応用として土日を除いた月末日の求め方の計３つの方法を行います。

日付から月末日を求める3つのメソッド

DateSerial関数を使った月末日の求め方
EoMonth関数を使った月末日の求め方
土日を除いた月末日の求め方

鳩野

月末日って関数で求められるんですね！

悠

どの方法も使い方は簡単なのでぜひ覚えてみて下さいね！

DateSerial関数を使った月末日の求め方

指定した年、月、日より該当する日付を返すDateSerial関数へ次の引数を指定すると月末日を求めることができます。

DateSerial関数を使った月末日の求め方

DateSerial(Year(日付),Month(日付)+1,0)

引数	内容
Year(日付)	Yaer関数で指定した日付の「年」を取得
Month(日付)+1	Month関数で指定した日付の「月」に１を足し、翌月を取得
0	０日を指定

悠

簡単にまとめると指定した日付の翌月１日の前日=当月末日を求めています。

鳩野

なるほど、１日の前日だから０で指定するんですね！

DateSirial関数の使用例と結果

次の処理を実行するとシート上のセルA２を基準の日付としてB2セルへ月末日の入力を行います。

Sub sample1()
    Dim startDate As Date
    
    '基準日を指定
    startDate = Range("A2").Value
    
    '********************************************************
    '日付を返す関数DateSerial(Year,Month,Day)の各引数に
    '各値を指定して基準の日付より月末日を求める
    'Year　⇒　Year(startDate):基準日の「年」を取得
    'Month ⇒　Month(startDate)+1:基準日の翌月(基準日の月+1)を取得
    'Day 　⇒　0:1日の前日(=0)を指定
    '********************************************************
    
    Range("B2").Value = DateSerial(Year(startDate), Month(startDate) + 1, 0) 
    
End Sub

EoMonth関数を使った月末日の求め方

ここでは基準の日付から指定した月の月末日を返す関数のEoMonth関数を使った月末日の求め方を見ていきます。

DateSerial関数では少し複雑な計算を行いましたが、EoMonth関数はもっと簡単に月末日を求めることができます。

EoMonth関数を使った月末日の求め方

EoMonth(日付,０)

引数	内容
日付	基準となる日付を指定(開始日)
０	基準の日付に対して0ヶ月後を指定

鳩野

結構シンプルな形ですね！

悠

内容を簡単にまとめると基準の日付に対して０ヶ月目の月末日＝当月末日を求めています。

EoMonth関数の使用例と結果

次の処理を実行するとシート上のセルA２を基準の日付としてB2セルへ月末日の入力を行います。

Sub sample2()
    Dim startDate As Date

    '基準日を指定
    startDate = Range("A2").Value
    
    '***********************************************************
    '日付のシリアル値を返す関数EoMonth(開始日,月)の引数に
    '各値を指定して開始日より月末日を求める
    '開始日　⇒　startDate:開始日を指定
    '月　　　⇒　0:開始日に対して0ヶ月(=当月)後の指定
    '************************************************************
    
    Range("B2").Value = Format(WorksheetFunction.EoMonth(startDate, 0), "yyyy/mm/dd")
    
End Sub

鳩野

先程のDateSirial関数と同じ結果ですね。

悠

また入力するセルの書式が「日付」以外の場合求めた月末日がシリアル値(数値)で表示されるので、その場合はformat関数で日付の書式を指定してみましょう。

土日を除いた月末日の求め方

先程求めた月末日と日付に対応する曜日を返すWeekday関数を組み合わせると土日を除いた月末日を求めることができます。

Weekday関数

Weekday(月末日,2)

引数	内容
月末日	基準となる日付を指定
2	週の基準を指定し、戻り値で基準値の日付の曜日を1(月曜)～7(日曜)の数値で返す

Weekday関数の使用例と結果

次の処理を実行するとシート上のセルA２を基準の日付としDateSerial関数で月末日を求めセルB2へ、Weekday関数で曜日を判定・調整後C2セルへ土日を除いた月末日の入力を行います。

Sub sample3()
    Dim startDate As Date
    Dim endDate As Date
    
    '基準日を指定
    startDate = Range("A2").Value

    '月末日を変数endDateへ格納
    endDate = DateSerial(Year(startDate), Month(startDate) + 1, 0)

    '書式を日付と曜日に指定してセルB2へ入力
    Range("B2").Value = Format(endDate, "yyyy/mm/dd(aaa)")
    
    '**********************************************
    'Weekday関数の戻り値から月末日の曜日を判定
    '戻り値が6なら-1,7なら-2を月末日から値を引く
    '戻り値が6,7以外(=平日の場合)は何もしない
    '**********************************************
    
    Select Case True
    
    'Weekday関数の戻り値が6(=土曜の場合)
    Case Weekday(endDate, 2) = 6
    
        endDate = endDate - 1
        
    'Weekday関数の戻り値が7(=日曜の場合)
    Case Weekday(endDate, 2) = 7
    
        endDate = endDate - 2
        
    End Select

    'weekday関数で判定後の月末日をセルC2へ入力
    Range("C2").Value = Format(endDate, "yyyy/mm/dd(aaa)")

End Sub