Download Practice Workbook
Overview of EoMonth Function in VBA
⏺ Function Objective
Returns the serial number for the last day of the month that is the implied number of months before or after start_date.
⏺ Syntax
The expression represents a WorksheetFunction object.
⏺ Arguments Explanations
Argument
Required/Optional
Data Type
Explanations
Arg1
Required
Variant
Start_date – a date that denotes the starting date. Enter the Dates by using the DATE function, or as a consequence of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May 2008. Dates in Text from will cause an error.
Arg2
Required
Variant
Months – the number of months before or after start_date. A positive value generates a forthcoming date; a negative value generates a past date.
⏺ Returns
It returns Double.
Important note:
Directly entering a date in a cell in the format 1/10/2022 will return the serial number 44571. Excel stores a date as a serial number. Change the serial number to date format using the Number Format option in Excel.
5 Examples of EoMonth Function in Excel VBA
To demonstrate our methods, we’ll use the following dataset of some dates in a column:
Example 1 – Find the Last Day of the Current Month Using EoMonth
Steps:
- Press Alt+F11 on your keyboard to open the Visual Basic Application.
- Select Insert > Module.
- Enter the following code in the module window:
Sub last_day()
Dim rng As Range
Set rng = Selection
Dim cell As Range
For Each cell In rng
cell.Offset(0, 1) = Application.WorksheetFunction.EoMonth(cell, 0)
Next cell
End Sub- Save the file.
- Select the range of cells B5:B12.
- Press Alt+F8 on your keyboard to open the Macro dialog box.
- Select last_day
- Cick on Run.
The dates are displayed as serial numbers.
- Select all the serial numbers.
- From the Home tab, click on Number.
- Select Short Date.
The serial numbers are now in date format.
Read more: How to Get the Day of Week Using VBA
Example 2 – Find the Last Day of the Previous Month
To find the last day of the previous month, we just have to enter a negative value in the code used in the previous example.
Steps:
- Press Alt+F11 on your keyboard to open the Visual Basic Application.
- Select Insert > Module.
- Enter the following code in the module window:
Sub previous_month()
Dim rng As Range
Set rng = Selection
Dim cell As Range
For Each cell In rng
cell.Offset(0, 1) = Application.WorksheetFunction.EoMonth(cell, -1)
Next cell
End Sub- Save the file.
- Select the range of cells B5:B12.
- Press Alt+F8 to open the Macro dialog box.
- Select previous_month.
- Click on Run.
The dates are shown as serial numbers.
- Select all the serial numbers.
- From the Home tab, click on Number.
- Select Short Date.
Read more: How to Get the Current Date in VBA
Example 3 – Find the Number of Days in a Month Using EoMonth
Here we’ll combine the EoMonth function with the Day function. The Day function gives us the day of the current date. The EoMonth function will return the last date and the Day function will extract the date as the final day.
Steps:
- Press Alt+F11 on your keyboard to open the Visual Basic Application.
- Select Insert > Module.
- Enter the following code in the module window:
Sub number_of_days()
Dim rng As Range
Set rng = Selection
Dim cell As Range
For Each cell In rng
cell.Offset(0, 1) = Day(Application.WorksheetFunction.EoMonth(cell, 0)) & " Days"
Next cell
End Sub- Save the file.
- Select the range of cells B5:B12.
- Press Alt+F8 on your keyboard to open the Macro dialog box.
- Select number_of_days.
- Click on Run.
The number of days in each month is returned.
Read more: How to Insert Current Date in Excel
Similar Readings
- Use IF Formula with Dates (6 Easy Examples)
- How to Format Date with VBA in Excel (4 Methods)
- Use VBA DateValue Function in Excel (6 Examples)
Example 4 – Find the First Day of a Month Using EoMonth
We will use a negative number in the argument to extract the last date of the previous month, then add 1 to this equation to get the next day, the first day of the next month.
Steps:
- Press Alt+F11 on your keyboard to open the Visual Basic Application.
- Select Insert > Module.
- Enter the following code in the module window:
Sub first_day()
Dim rng As Range
Set rng = Selection
Dim cell As Range
For Each cell In rng
cell.Offset(0, 1) = Application.WorksheetFunction.EoMonth(cell, -1) + 1
Next cell
End Sub- Save the file.
- Select the range of cells B5:B12.
- Press Alt+F8 on your keyboard to open the Macro dialog box.
- Select first_day.
- Click on Run.
The dates are shown as serial numbers.
- Select all the serial numbers.
- From the Home tab, click on Number.
- Select Short Date.
We have successfully found the first day of a month using the EoMonth function in VBA.
Read more: How to Calculate Due Date with Formula in Excel
Example 5 – Find the End Date of a Future Month
Here, we want to find the end date of a month four months away from our dataset, so we will enter 4 as our EoMonth argument.
Steps
- Press Alt+F11 on your keyboard to open the Visual Basic Application.
- Select Insert > Module.
- Enter the following code in the module window:
Sub future_month()
Dim rng As Range
Set rng = Selection
Dim cell As Range
For Each cell In rng
cell.Offset(0, 1) = Application.WorksheetFunction.EoMonth(cell, 4)
Next cell
End Sub- Save the file.
- Select the range of cells B5:B12.
- Press Alt+F8 on your keyboard to open the Macro dialog box.
- Select future_month.
- Click on Run.
The dates are shown as serial numbers.
- Select all the serial numbers.
- From the Home tab, click on Number.
- Select Short Date.
We have successfully found the last day of the future month.
Read more: How to Use Excel VBA MONTH Function
Things to Remember
- If you provide a decimal in the month argument, it will only use the integer portion in start_date.
- By default, January 1 1900 is serial number 1.
- If start_date is not a valid date, EoMonth will return the #NUM! error.
- EoMonth will also return the #NUM! error if start_date plus months produces an invalid date.
Further Reading
- Date Variable in VBA Codes (7 Uses of Macros with Examples)
- Convert Date from String Using VBA (7 Ways)
- How to Use the VBA DateAdd Function in Excel
