Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by jstevens View Post
    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
    Regards
    Don

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Central Texas, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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
    [MS MVP - Word]

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    30041
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    30041
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    What's long about Paul's function?
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    New Lounger
    Join Date
    Dec 2009
    Location
    30041
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by Bill Botzong View Post
    (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. #12
    New Lounger
    Join Date
    Dec 2009
    Location
    Central Texas, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #13
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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
    [MS MVP - Word]

  14. #14
    New Lounger
    Join Date
    Apr 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
  •