Last day of Month? 3

[RichS]

Hey gang,
Is there a function in VB that will return the last day of the month if you provide a certain day? Oh, I don't know... something like vbLastDayOfMonth(l_dteDate).

TIA.

 

[bobhilt]

This should do the trick:
1. Subtract the day date part minus one from the date to get you the first day of the month. (Mathematical equiv of adding 1 - day of month)
2. Add a month.
3. Subtract a day.

You can probably make this look cleaner with intermediate steps, but this should get the job done:

Public Function LastDayOfMonth(p_dtDate) as date
  LastDayOfMonth = DateAdd("d", -1, DateAdd("m", 1, DateAdd("d", 1 - DatePart("d", p_dtDate), p_dtDate)))
End Function

--Bob

 

[WildBillH]

Try this (it works in VB5 and VB6). Of course, the "bad boy" of all months is February, because of leap years.


Public Function LastDayOfMonth(dInput As Date) As Integer
Dim iWeekDay As Integer
Select Case Month(dInput)
Case 4, 6, 9, 11 '30-day months.
LastDayOfMonth = 30
Case 1, 3, 5, 7, 8, 10, 12 '31-day months.
LastDayOfMonth = 31
Case 2 'February
LastDayOfMonth = 28
On Error GoTo LastDayOfMonth_Done
iWeekDay = WeekDay("02/29/" & Str$(Year(dInput)))
LastDayOfMonth = 29
Case Else 'Bad date.
LastDayOfMonth = 0
End Select
LastDayOfMonth_Done:
On Error GoTo 0
End Function

 

[jmarler]

bobhilt has the right idea. - Jeff Marler B-)

 

[WildBillH]

Jeff is right -- Bob's solution is pretty slick! It also returns the complete date, where mine only returns the "day" portion of the date.

Kudos, Bob, and thanks for the idea.

 

[RichS]

Very slick. Thanks for the solutions.
Rich

 

[MichaelRed]

Much ado about nothing. See below. VBA knows all about the various numbers of days in months, including leap year.

More to the point, Date serial doesn't CARE about which day of the month you want to find, as long as it is (more or less) an integer.

I did this as a function and used 'RichS' nomencalture, but -IMHO - the whole thing isn't really worth the effort / overhead of a seperate "Function". Just throw the single statement in-line wherer ever necessary [of course also relpacing the arg with a valid date (like NOW)].

Public Function vbLastDayOfMonth(l_dteDate As Date) As Date

    'Usage/Example
    'vbLastDayOfMonth(Now)      :Note run on 6/22/01
    '6/30/01
    vbLastDayOfMonth = DateSerial(Year(l_dteDate), Month(l_dteDate) + 1, 0)

End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[jjames]

I use this...

dtLastDay = DateSerial(Year(dtAnyDate), Month(dtAnyDate) + 1, 1) - 1

 

[MichaelRed]

jjames,


Four key strokes and one calculation more? Why? Dont you understand that the "zeroth" day of ANY month HAS to be the Last day of the previous (month)? It is really QUITE simple.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[RichS]

MichaelRed,
I don't know about jjames, but I did not know that. Thanks for the heads up. (It would be ok with me if you left the attitude out, though).

Rich

 

[MichaelRed]

Rich,

I did not mean the "attitude" to be directed at you. I TRY to only post things which I believe are a CONTRIBUTION to the thread. When I am wrong, I TRY to learn from my errors and admit them. Contributing to a thread, for me, requires that I at least read the previous postings. For jjames's post, he either did not read the provious or he doesn't understand what he is doing or he just doesnt care that his post is regressing from previous posted item(s). If my 'attitude' is evident in the post, it is my frustration at post's like the one jjames left.
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[jjames]

MichaelRed,

I'm not sure why my post 'frustrates' you? QUITE simply, when I read this thread, your post was not here. I'm guessing you've never answered a question only to find that someone had beaten you to it while you were busy composing your post. And hey, I did not know that the "zeroth" day of ANY month HAS to be the Last day of the previous (month)?

Jase