Excel VBA: Get the Last Day of a Month

In Excel VBA, use DateSerial(Year(d), Month(d) + 1, 0) to get the final date of the month containing a date. The zero day means the day before the first of the following month, so the same expression handles February, leap years, and December without a separate month-length check.

The simplest VBA solution

Put this function in a standard VBA module. It accepts a valid Excel date and returns a VBA Date value for the last calendar day of that date’s month.

Public Function LastDayOfMonth(ByVal InputDate As Date) As Date
    ' Day zero of the following month is this month's final day.
    LastDayOfMonth = DateSerial(Year(InputDate), Month(InputDate) + 1, 0)
End Function

For example, if InputDate is 14 June 2026, the function returns 30 June 2026. If it is 8 February 2024, it returns 29 February 2024. The result is a date value, not a text string; Excel can use it in comparisons, calculations, sorting, and other date operations.

The key is to pass the date you want to inspect, rather than a month number or a formatted date string. The expression takes the year and month from that date, advances to the next month, and asks VBA to construct day zero of it.

Why DateSerial with day zero works

DateSerial creates a date from year, month, and day components. VBA normalizes values that run beyond the usual calendar range: a month number of 13 rolls into January of the next year, and a day number of zero resolves to the final day of the preceding month. Thus DateSerial(2026, 7, 0) produces 30 June 2026.

This behavior is useful because the code does not need to know whether a month has 28, 29, 30, or 31 days. The date system handles those calendar rules. In a leap year, February’s last day is 29; in a non-leap year it is 28. December works as well: adding one to month 12 rolls into January of the next year, and day zero returns 31 December of the original year.

Using Day(DateSerial(Year(d), Month(d) + 1, 0)) would return the day number, such as 30 or 31, rather than the full date. Use the original DateSerial expression when the goal is a date value.

How to add and use the function

Open the workbook in Excel for Microsoft 365 on Windows and press Alt+F11 to open the Visual Basic Editor. On Mac, use Option+F11; depending on the keyboard, you may also need to hold Fn.
In the editor, choose Insert > Module. Paste the function into the new standard module, not into a worksheet’s code window or the ThisWorkbook module.
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain the VBA code. A normal .xlsx file cannot store VBA macros.
In a worksheet, enter a valid date in A2. In B2, enter =LastDayOfMonth(A2). Format B2 as a date if Excel displays a serial number instead.

Before running or enabling any macro in a workbook, save a copy of the file. Macro actions generally cannot be undone with Ctrl+Z. This particular function returns a value rather than changing other cells, but the backup reminder matters if you later add code that edits the workbook. Excel for the web can display a workbook containing VBA, but it cannot run or edit VBA macros; use the desktop application.

Using the function in a worksheet

With a valid date in A2, enter =LastDayOfMonth(A2) in B2. The formula calls the VBA function each time Excel recalculates the worksheet. Copy it down alongside a list of dates to calculate a month-end date for each row. For example, an entry of 3/11/2026 returns the final day of November 2026, provided Excel recognizes the entry as a date according to the workbook’s regional settings.

VBA functions used this way are called user-defined functions, or UDFs. They are useful when a workbook already relies on VBA or when you need the same custom logic in many cells. The function above is read-only: it calculates and returns a result. It does not place values in cells, format a range, or modify the workbook.

To show only the month-end day number, apply a number format such as d; to show a readable date, choose a date format such as d mmm yyyy. Formatting changes how the value appears, not the underlying date. This distinction matters if another formula later compares the result with a date or uses it in date arithmetic.

Excel formula alternative: EOMONTH

If you do not need VBA, Excel’s built-in EOMONTH function is often simpler. With the date to evaluate in A2, enter =EOMONTH(A2,0). The zero tells Excel to return the end of the same month as A2. A positive month offset returns a later month’s end, while a negative offset returns an earlier month’s end; for example, an offset of 1 means the end of the next month.

EOMONTH returns a date serial, so apply a date number format if the cell shows a number. It is available in current Microsoft 365 Excel and does not require you to write or enable VBA. In older Excel releases, the function may require the Analysis ToolPak add-in; in current releases it is built in. Unlike the UDF, the worksheet formula does not require saving a macro-enabled file.

Choose the VBA function when a VBA project needs reusable code, or when another macro will call the same date logic. Choose EOMONTH for an ordinary worksheet calculation, especially when the workbook must remain an .xlsx file or be usable in Excel for the web. You can also call Excel’s worksheet function from VBA, but that adds a dependency on Excel’s worksheet-function interface without improving this straightforward calculation.

Common problems and how to fix them

The cell shows a number. Excel stores dates as serial values. Apply a date format to the result cell; the calculation may already be correct.
The formula name is not recognized. Confirm that the code is in a standard module, the function name in the cell matches the code, and macros are enabled for this trusted workbook. Check that the file was saved in a macro-enabled format.
The input produces an error. The function’s argument is declared as Date, so it expects a valid date value. A blank, arbitrary text, or an invalid date may cause an error. Check the source cell and its regional date interpretation before changing the code.
The displayed result seems one day early or late. Check the input’s actual value and the cell’s number format. A date displayed as text can look plausible while not being a real Excel date; changing its format alone does not convert text into a date.
It works on one computer but not another. A workbook’s VBA may be blocked by macro security settings, or the other user may be opening it in Excel for the web. Use the desktop app and only enable code from a source you trust.

For a macro that processes a range instead of using a worksheet UDF, validate each input before passing it to the function. A loop should skip blank cells or handle non-date values explicitly, rather than assuming every cell contains a usable date. This keeps one bad entry from interrupting a larger procedure.

Date values, display formats, and time

VBA’s Date type represents a date and can also include a time. The DateSerial expression returns the last day at midnight, so it does not preserve the time portion of the input. If the input is 14 June 2026 at 3:30 PM, the result is still 30 June 2026 at 12:00 AM. This is normally what a month-end date calculation needs.

If you need the final instant of a month for a timestamp comparison, do not assume that a date formatted to look like month-end includes the whole final day. A more reliable test for whether a timestamp belongs to a given month is to include the first day of that month and exclude the first day of the next month. That avoids relying on a guessed final time such as 11:59:59 PM, which can be problematic when time precision differs.

Also distinguish a calendar month from a fixed number of days. Adding 30 days to a date does not reliably reach the end of its month: months have different lengths, and the result can land in the following month. The DateSerial method derives the boundary from calendar components instead.

When this approach is most useful

The DateSerial pattern is a compact way to calculate a calendar boundary, and it remains readable to anyone familiar with the function’s normalization behavior. It works well for invoice periods, monthly reports, due-date checks, and grouping records by month. For instance, a macro can compare each transaction’s month-end date with a reporting cutoff without maintaining a table of month lengths.

Use it only when the intended calendar is the one represented by the Excel/VBA date system. It is not a substitute for a business calendar that excludes weekends or holidays, nor does it determine a fiscal period when the fiscal year starts in a month other than January. Those tasks need additional rules, such as a holiday list or a fiscal-calendar mapping.

For international workbooks, store and pass real date values rather than building date strings such as 6/7/2026. That text can be interpreted as June 7 or July 6 depending on regional settings. The VBA expression uses the year, month, and day of the date value itself, avoiding ambiguity introduced by manually parsing a string.

Frequently Asked Questions

Does this work in a leap year?
Yes. DateSerial applies the calendar rules, so February 2024 returns 29 February and February 2025 returns 28 February.

How do I get the last day of the previous month?
Pass any date from the month after the one you want. For example, passing a date in June returns the end of June, so use the appropriate month’s date as the input. In a worksheet, =EOMONTH(A2,-1) returns the end of the month before the date in A2.

Can I return just 30 or 31 instead of a date?
Yes. Wrap the calculation in VBA’s Day function: Day(DateSerial(Year(InputDate), Month(InputDate) + 1, 0)). That result is a number, not a date, so use the original expression if you need to compare or calculate with the month-end date.

Why does my result display as 46204?
That is likely the date’s serial number rather than a failed calculation. Select the result cell and apply a date number format. The exact serial depends on the date returned and Excel’s date system.

Can Excel for the web run this VBA function?
No. Excel for the web can open and display a macro-enabled workbook, but VBA macros do not run there. Open the workbook in desktop Excel to use the UDF, or replace it with the built-in EOMONTH worksheet formula when appropriate.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.