• Status: Solved
• Priority: Medium
• Security: Public
• Views: 175

# Formula to find the last day of the month

I need to find the last day of the current month (it could be any month).
0
rashida
1 Solution

Commented:
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))
End Function

to call:

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

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

Commented:
For current month:

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

Billing EngineerCommented:
To find the LAST day of the current month, use this:
(you may replace Now by the date you want)
0

Commented:
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"
End Function
0

Author Commented:
This was the easiest of all.

Thanks!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.