[VBA Beginner] How to Get the Last Day of the Month | Date Processing with DateSerial Function

2025年12月29日

When working with dates in Excel VBA, there are many situations where you want to get the “last day of a specified month.”

Common examples include creating reports that close at the end of the month or specifying a range from the beginning to the end of the month.

This article explains how to use the DateSerial function to get “the last day of the month to which a date belongs.”

How to Get the Last Day Using DateSerial

To find the last day of the month in VBA, use the following logic:

Find “1 day before the 1st of the next month.”

Based on this idea, you can get the exact last day using DateSerial.

Code Example 1: General Process to Get the End of the Month

Sub GetEndOfMonthDate()
    Dim baseDate As Date
    Dim endOfMonth As Date

    baseDate = Range("D3").Value
    endOfMonth = DateSerial(Year(baseDate), Month(baseDate) + 1, 0)

    MsgBox "月末日は：" & endOfMonth
End Sub

Key Points:

By using DateSerial(Year, Month + 1, 0), you can automatically get “Day 0 of the next month,” which equals “the last day of the current month.”
You need to enter a date (e.g., 2025/7/10) in Range("D3").Value.

Code Example 2: Calculating by Specifying the 1st Day

MsgBox DateSerial(Year(Range("D3").Value), _
                  Month(Range("D3").Value) + 1, _
                  1) - 1

The method of “subtracting 1 day from the 1st of the next month” is also effective.

Note: Handling Year Rollovers

Even at the end of the year, such as in December, Month(baseDate) + 1 works without problems.

For example, even for “December 10, 2025,” DateSerial(2025, 13, 0) will automatically be processed as “December 31, 2025.”

This is because the DateSerial specification appropriately adjusts the year even if the number of months is 13 or more.

Summary | DateSerial is the Best Solution

If you want to accurately get the last day of the month in Excel VBA, using the DateSerial function is the safest and simplest method.

Derive the last day of the current month by specifying “Day 0 of the next month” or “subtracting 1 day from the 1st of the next month.”
It can handle leap years and months with different end dates.
DateSerial(Year, Month + 1, 0) automatically handles year rollovers.

This is very useful in situations requiring accurate management of date boundaries, such as report processing and monthly aggregation. Please incorporate it into your automated processes.

よかったらシェアしてね！