VBA Solution to EOMonth

Topic

New Reply

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

Reply | Quote

Replies

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

   If IsMissing(MonthsToAdd) Then
      MonthsToAdd = 0
   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

Reply | Quote

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

Reply | Quote

This should work:

Code:

MyDate = MyDate - DatePart("d",MyDate)+1
MyDate = DateAdd("m",1,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.

Reply | Quote

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

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

What’s long about Paul’s function?

Reply | Quote

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).

Reply | Quote

(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.

Reply | Quote

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.

Reply | Quote

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.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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.

Reply | Quote