last day of month

how can i get the last day of a current month withought looping from 31 and down?
thnaks i nadvance
peleg

I think you can't get it with one line code or single function call.

You must write a function for this or you can construct an array include every month's last day. But in both two ways you must consider leap years also.

May be it is easier to find the next month's first day then step back 1 day by using dateadd function.

Like this:

VB Code:

Option Explicit
 
Private Sub Form_Load()
  Dim a As Integer
  Dim b As Integer
  Dim c As Date
  Dim lastday As Date
  a = Month(Now) + 1
  b = Year(Now)
  If a = 13 Then a = 1: b = b + 1 ' Special code for December
  c = Format(a & "/1/" & b, "mm/dd/yy")
  lastday = DateAdd("d", c, -1)
  MsgBox lastday & "  " & Format(lastday, "ddd")
End Sub

One more for the library :wave:

David you use Format function to set the date. What is the difference, in fact what is the advantages/disadvantages of using DateSerial function instead of Format?
I have to use DateSerial function because in here people use different date formats, some use US (mm/dd/yy) but some use Turkish (dd/mm/yyyy). As a result I think I will have problems with Format. What do you think?

another using dateserial.

VB Code:

Format(DateSerial(Year(Now), Month(Now) + 1, 0), "ddd")

casey.

There really is no difference. Personal preference.
format uses the format of the machine, you have to use that
not an issue in the US, though

Wasn't sure if you wanted the date or the day, so I included both.

Casey, nice trick. Didn't think to use 0.

Try This One....

VB Code:

Function FindLastDay(ddate As Date) As Integer
    ddate = DateAdd("d", 1 - Day(ddate), ddate)
    FindLastDay = DatePart("d", DateAdd("d", -1, DateAdd("M", 1, ddate)))
End Function