Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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