Thread: VBA Solution to EOMonth

1. Is there a VBA solution for the Excel formula "=EOMonth(MyDate,0)"

Where the variable MyDate = 12/13/2009

The expected results would be 12/31/2009

Thanks,
John

2. I found something online:

Code:
```Public Function EOMonth(InputDate As Date, Optional MonthsToAdd As Integer)
' Returns the date of the last day of month, a specified number of months
' following a given date.
Dim TotalMonths As Integer
Dim NewMonth As Integer
Dim NewYear As Integer

End If

TotalMonths = Month(InputDate) + MonthsToAdd
NewMonth = TotalMonths - (12 * Int(TotalMonths / 12))
NewYear = Year(InputDate) + Int(TotalMonths / 12)

If NewMonth = 0 Then
NewMonth = 12
NewYear = NewYear - 1
End If

Select Case NewMonth
Case 1, 3, 5, 7, 8, 10, 12
EOMonth = DateSerial(NewYear, NewMonth, 31)
Case 4, 6, 9, 11
EOMonth = DateSerial(NewYear, NewMonth, 30)
Case 2
If Int(NewYear / 4) = NewYear / 4 Then
EOMonth = DateSerial(NewYear, NewMonth, 29)
Else
EOMonth = DateSerial(NewYear, NewMonth, 28)
End If
End Select
End Function```

3. Originally Posted by jstevens
Is there a VBA solution for the Excel formula "=EOMonth(MyDate,0)"

Where the variable MyDate = 12/13/2009

The expected results would be 12/31/2009

Thanks,
John
Try the following:
Code:
`MonthEnd = DateSerial(year(now),month(now)+1,0)`
HTH

4. This should work:

Code:
```MyDate = MyDate - DatePart("d",MyDate)+1
This changes the day to 1 (to avoid issues with 31-to-30 or the short February), then adds a month, then subtracts 1.

--Scott.

5. Hi John,

Like the code you found, the following allows you to specify the number of months to add:
Code:
```Public Function EndOfMonth(ByVal InputDate As Date, Optional AddMonths As Integer) As Date
EndOfMonth = DateSerial(Year(InputDate), Month(InputDate) + AddMonths + 1, 0)
End Function```

6. I use something like this which picks the first day of the next month and subtracts 1 from it.

wDate = '12/13/2009' ' define date

wEOM = datevalue(month(wDate)+1 & "/01/" & year(wDate)) - 1

you have to trap for period 12 because the date function does not understand period 13 and assumes you've started using European date convention. So add:

if month(wDate) = 12 then
wEOM = datevalue("12/31/" & year(wDate))
else
wEOM = datevalue(month(wDate)+1 & "/01/" & year(wDate)) - 1
end if

7. DateValue uses the system settings, so if a user uses a different system date format, the result may not be what you expect. The other suggested solutions do not depend on US date format, so they are more universal.

8. Agreed, but its far simpler code even if you have to transpose your date format that some of the other suggestions. Subtracting one from the first day of the next month makes the system do the math rather than relying on long, complicated code.

9. What's long about Paul's function?

10. nothing long nor wrong... it's a function and you have to put code to call it. i like simplicity of inline code. it's a personal choice :-) (and I don't see a paul above, but both are equally suitable).

11. Originally Posted by Bill Botzong
(and I don't see a paul above, but both are equally suitable).
Macropod's first name is Paul - see his signature in post #5 in this thread.

12. Other than the first reply, which seems unnecessarily complex and doesn't calculate leap year correctly, all of the suggestions are only 1 or 2 lines long. The only other function is a single line long,

The advantage of a function is that it's much easier to fix if you later discover a problem with it. If you do it inline, you'll likely have lots of trouble even finding all of the places to fix.

Lessee, with only the Day function:
Code:
```Public Function EOMonth(InputDate As Date)
EOMonth = InputDate - day(InputDate) +45
EOMonth = EOMonth - day(EOMonth)
end sub```
--Scott.

13. Hi Bill,

Don's request was for"a VBA solution for the Excel formula". That's what I and others have provided. I don't know what it is that makes you'd think "you have to put code to call it" means it is at all complicated - it works pretty much the same as a formula. For example mine can be called as:
=EndOfMonth(A1)
or:
=EndOfMonth(A1, 3) - for the last day of the month three months hence.

That's no more complex than any inbuilt Excel function.

14. Hi,
This is probably very late, but I hope someone will find it useful. I guess I'm just being lazy, but I like Excel to do the work when possible. How about

Code:
```Increment = 0
NewDate = Evaluate("EOMONTH(" & MyDate & "," & Increment & ")")```
and you can change the value of Increment as you like.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•