Get the last day of month

Question

I want to get the last day of the month.

This is my code. If I want to debug it and compile it to the database it says it has an error in the syntax.

Public Function GetNowLast() As Date
    Dim asdfh As Date
    asdfh = DateValue("1." _
      & IIf(Month(Date) + 1) > 12, Month(Date) + 1 - 12, Month(Date) + 1) _
      &"."&IIf(Month(Date)+1)>12 , Year(Date)+1,Year(Date))
    asdf = DateAdd("d", -1, asdf)
    GetNowLast = asdf
End Function

Answer 1

GD Linuxman,

Let's focus on obtaining the result...:-)

See also: here

The comment by @Scott Craner is spot on ! Though strictly speaking there is no need to use the formatting. (Assuming you want to work with the 'Date' object)

To achieve what you want, setup the function as per below:

Function GetNowLast() as Date

    dYear = Year(Now)
    dMonth = Month(Now)

    getDate = DateSerial(dYear, dMonth + 1, 0)

    GetNowLast = getDate

End Function

You can call the function in your code as:

Sub findLastDayOfMonth()

    lastDay = GetNowLast()

End Sub

Alternatively, and neater is likely:

Function GetNowLast(inputDate as Date) as Date

    dYear = Year(inputDate)
    dMonth = Month(inputDate)

    getDate = DateSerial(dYear, dMonth + 1, 0)

    GetNowLast = getDate

End Function

You can call that function and pass it an input parameter.

Sub findLastDayOfMonth()

lastDay = GetNowLast(Now()) 'Or any other date you would like to know the last day of the month of.

End Sub

See also this neat solution by @KekuSemau

Answer 2

Sub Worksheet_SelectionChange(ByVal Target As Range)

   Dim d1 As String

   Set Rng = Range("A2")
   d1 = Range("a2").Value2 'put a date in A2 Formatted as date(cell format)

   Dim years
   Dim months
   Dim end_month
   years = year(d1)
   months = month(d1)

   end_month = Day(DateSerial(years, months + 1, 1 - 1)) 'add one month and subtract one day from the first day of that month

   MsgBox CStr(end_month), vbOKOnly, "Last day of the month"


End Sub