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

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
Asked:
rashida
1 Solution
 
arcusdCommented:
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
 
Erick37Commented:
Dim dDate As Date
dDate = #2/2/00#
Debug.Print Day(DateSerial(Year(dDate), Month(dDate) + 1, 0))
0
 
Erick37Commented:
For current month:

Debug.Print Day(DateSerial(Year(Date), Month(Date) + 1, 0))
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
janneaCommented:
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
 
rashidaAuthor 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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now