Solved

# Formula to find the last day of the month

Posted on 2000-04-10
Medium Priority
171 Views
Last Modified: 2010-05-02
I need to find the last day of the current month (it could be any month).
0
Question by:rashida
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
6 Comments

LVL 4

Expert Comment

ID: 2702694
Function MonthLastDay(mdate As Variant) As Variant
On Error Resume Next
Dim thismo, nextmo, thisyr, nextyr As Integer
Dim mstr As String
thismo = Month(mdate)
thisyr = Year(mdate)
If thismo = 12 Then
nextmo = 1
nextyr = thisyr + 1
Else
nextmo = thismo + 1
nextyr = thisyr
End If
mstr = Trim(Str(nextmo)) + "/01/" + Trim(Str(nextyr))
MonthLastDay = DateAdd("d", -1, CDate(mstr))
End Function

to call:

x = MonthLastDay(#12/01/1999#)
0

LVL 32

Expert Comment

ID: 2702697
Dim dDate As Date
dDate = #2/2/00#
Debug.Print Day(DateSerial(Year(dDate), Month(dDate) + 1, 0))
0

LVL 32

Expert Comment

ID: 2702771
For current month:

Debug.Print Day(DateSerial(Year(Date), Month(Date) + 1, 0))
0

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 100 total points
ID: 2703232
To find the LAST day of the current month, use this:
DateAdd("d", -1, DateAdd("m", 1, DateSerial(Year(Now), Month(Now), 1)))
(you may replace Now by the date you want)
0

LVL 1

Expert Comment

ID: 2703596
Function LastDay(ByVal XDate As Variant) As Variant
If Not IsDate(XDate) Then MsgBox "Error", 16: Exit Function
XDate = Format\$(XDate, "yyyy-mm-dd")
XDate = Left\$(XDate, 8) & "01"
XDate = DateAdd("d", -1, XDate)
LastDay = DateAdd("m", 1, XDate)
End Function
0

Author Comment

ID: 2703768
This was the easiest of all.

Thanks!!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
###### Suggested Courses
Course of the Month10 days, 19 hours left to enroll

#### 719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.