By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

What's wrong with this get last day of month function?

P: n/a
MLH
Public Function GetLastDayOfMonth(ByVal dtDay As Date) As Date
'************************************************* *************************
' Accepts a date. Determines month & year of the date. Returns
' the date of the last day of that month (in the same year)
'************************************************* *************************
GetLastDayOfMonth = DateSerial(Year(dtDay), Month(dtDay), 31)

End Function

If I enter dtDay value of 6/15/2005, the FN returns 7/1/2005 instead
of 6/30/2005. What's wrong?
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
MLH wrote:
Public Function GetLastDayOfMonth(ByVal dtDay As Date) As Date
'************************************************* *************************
' Accepts a date. Determines month & year of the date. Returns
' the date of the last day of that month (in the same year)
'************************************************* *************************
GetLastDayOfMonth = DateSerial(Year(dtDay), Month(dtDay), 31)

End Function

If I enter dtDay value of 6/15/2005, the FN returns 7/1/2005 instead
of 6/30/2005. What's wrong?


Not all months have 31 days. Use...

GetLastDayOfMonth = DateSerial(Year(dtDay), Month(dtDay) + 1, 0)

DateSerial is smart enough to know that the zeroth of a month is equal to the
last day of the previous month regardless of how many days are in that month.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #2

P: n/a
MLH
That's pretty sneaky, Rick. I like it. Thx a bunch.
GetLastDayOfMonth = DateSerial(Year(dtDay), Month(dtDay) + 1, 0)


Nov 13 '05 #3

P: n/a
On Sun, 10 Jul 2005 19:23:00 GMT, "Rick Brandt" <ri*********@hotmail.com>
wrote:
MLH wrote:
Public Function GetLastDayOfMonth(ByVal dtDay As Date) As Date
'************************************************* *************************
' Accepts a date. Determines month & year of the date. Returns
' the date of the last day of that month (in the same year)
'************************************************* *************************
GetLastDayOfMonth = DateSerial(Year(dtDay), Month(dtDay), 31)

End Function

If I enter dtDay value of 6/15/2005, the FN returns 7/1/2005 instead
of 6/30/2005. What's wrong?


Not all months have 31 days. Use...

GetLastDayOfMonth = DateSerial(Year(dtDay), Month(dtDay) + 1, 0)

DateSerial is smart enough to know that the zeroth of a month is equal to the
last day of the previous month regardless of how many days are in that month.


That works, but it's very confusing to understand how. Here's another method
that takes more code, but I like it better anyway...

Public Function GetLastDayOfMonth(ByVal dtDay As Date) As Date

Dim dtFirstOfMonth As Date
dtFirstOfMonth = DateAdd("d", 1 - Day(tdDay), tdDay)

Dim dtFirstOfNextMonth As Date
dtFirstOfNextMonth = DateAdd("m", 1, dtFirstOfMonth)

GetLastDayOfMonth = DateAdd("d", -1, dtFirstOfNextMonth)
End Function

Nov 13 '05 #4

P: n/a
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:n5********************************@4ax.com...
On Sun, 10 Jul 2005 19:23:00 GMT, "Rick Brandt" <ri*********@hotmail.com>
wrote:
MLH wrote:
Public Function GetLastDayOfMonth(ByVal dtDay As Date) As Date
'************************************************* *************************
' Accepts a date. Determines month & year of the date. Returns
' the date of the last day of that month (in the same year)
'************************************************* *************************
GetLastDayOfMonth = DateSerial(Year(dtDay), Month(dtDay), 31)

End Function

If I enter dtDay value of 6/15/2005, the FN returns 7/1/2005 instead
of 6/30/2005. What's wrong?


Not all months have 31 days. Use...

GetLastDayOfMonth = DateSerial(Year(dtDay), Month(dtDay) + 1, 0)

DateSerial is smart enough to know that the zeroth of a month is equal to
the
last day of the previous month regardless of how many days are in that
month.


That works, but it's very confusing to understand how. Here's another
method
that takes more code, but I like it better anyway...

Public Function GetLastDayOfMonth(ByVal dtDay As Date) As Date

Dim dtFirstOfMonth As Date
dtFirstOfMonth = DateAdd("d", 1 - Day(tdDay), tdDay)

Dim dtFirstOfNextMonth As Date
dtFirstOfNextMonth = DateAdd("m", 1, dtFirstOfMonth)

GetLastDayOfMonth = DateAdd("d", -1, dtFirstOfNextMonth)
End Function


If it's wrapped in a function, I don't see what it matters if not everyone
understands how it works. The name of the function makes it obvious what
it's supposed to be doing.

Public Function GetLastDayOfMonth(ByVal dtDay As Date) As Date
GetLastDayOfMonth = DateSerial(Year(dtDay), Month(dtDay) + 1, 0)
End Function
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Nov 13 '05 #5

P: n/a
Br
Steve Jorgensen <no****@nospam.nospam> wrote:
On Sun, 10 Jul 2005 19:23:00 GMT, "Rick Brandt"
<ri*********@hotmail.com> wrote:
MLH wrote:
Public Function GetLastDayOfMonth(ByVal dtDay As Date) As Date
'************************************************* *************************
' Accepts a date. Determines month & year of the date. Returns
' the date of the last day of that month (in the same year)
'************************************************* *************************
GetLastDayOfMonth = DateSerial(Year(dtDay), Month(dtDay), 31)

End Function

If I enter dtDay value of 6/15/2005, the FN returns 7/1/2005 instead
of 6/30/2005. What's wrong?


Not all months have 31 days. Use...

GetLastDayOfMonth = DateSerial(Year(dtDay), Month(dtDay) + 1, 0)

DateSerial is smart enough to know that the zeroth of a month is
equal to the last day of the previous month regardless of how many
days are in that month.


That works, but it's very confusing to understand how. Here's
another method that takes more code, but I like it better anyway...

Public Function GetLastDayOfMonth(ByVal dtDay As Date) As Date

Dim dtFirstOfMonth As Date
dtFirstOfMonth = DateAdd("d", 1 - Day(tdDay), tdDay)

Dim dtFirstOfNextMonth As Date
dtFirstOfNextMonth = DateAdd("m", 1, dtFirstOfMonth)

GetLastDayOfMonth = DateAdd("d", -1, dtFirstOfNextMonth)
End Function


I use.....

LastDayOfMonth = DateAdd("d", -1, DateSerial(Year(myDate), Month(myDate)
+ 1, 1))
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 13 '05 #6

P: n/a
Br@dley wrote:
I use.....

LastDayOfMonth = DateAdd("d", -1, DateSerial(Year(myDate),
Month(myDate) + 1, 1))


But why have the overhead of running two functions when DateSerial is perfectly
capable of doing this by itself?

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


Nov 13 '05 #7

P: n/a
Br
Rick Brandt <ri*********@hotmail.com> wrote:
Br@dley wrote:
I use.....

LastDayOfMonth = DateAdd("d", -1, DateSerial(Year(myDate),
Month(myDate) + 1, 1))


But why have the overhead of running two functions when DateSerial is
perfectly capable of doing this by itself?


I was just saying that _was_ how I did it compared to the several lines
of code used in the previous post. Obviously now I know zero has the
same effect I'll use that.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.