本記事ではExcel VBAで日付を使った月末日の求め方を3つ解説していきます。
Excelで作業をしているとこんな場面で月末日を求める事が多くありませんか？
- 請求書作成や給与計算
- 月次の報告書作成
- プロジェクト進捗の確認
鳩野言われてみると確かにそうかも。。。
しかし毎回手作業で月末日を確認するのは手間がかかる上、毎月日付が異なるためミスも発生しやすくなりがちです。
悠そんな時に役立つのがExcel VBAを使った月末日の求め方です。
本記事の方法を使えば日付から自動で月末日を計算し、作業時間の短縮やミスの軽減へと繋げる事ができます。
Excel VBAで日付から月末日を求められる方法が分かり、作業時間短縮やミスの軽減に繋げる事ができる。
日付から月末日を求める3つのメソッド
今回はExcel VBAで関数を使った２つの月末日の求め方と応用として土日を除いた月末日の求め方の計３つの方法を行います。
- DateSerial関数を使った月末日の求め方
- EoMonth関数を使った月末日の求め方
- 土日を除いた月末日の求め方
鳩野月末日って関数で求められるんですね！
悠どの方法も使い方は簡単なのでぜひ覚えてみて下さいね！
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(日付,０)
| 引数 | 内容 |
|---|---|
| 日付 | 基準となる日付を指定(開始日) |
| ０ | 基準の日付に対して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(月末日,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
悠月末日が2024/11/30(土)なので土日を除いた月末日は2024/11/29(金)となります。
終わりに
以上で今回の解説は終了となります。
Excel VBAを用いる事で月末日を自動で簡単に計算でき、作業時間短縮やミスの軽減に繋げる事ができます。
今回解説した内容はどれも数行で終わるものなので月末日を求めるのに悩んだ時は気軽に試してみましょう！
また本ブログではExcel VBAやExcelの知っていると便利な機能や使い方についてもまとめているので宜しければ参考にしてみて下さい。
