last day of month

[Guest]

I would like a function that calculates the last day of the month.

But I need the function to be smart enough to work out that Feb only 28 days
and June has 30 etc.

I already have the function for 1st working day of the month and this works
very well.

Please help

 

[Guest]

LastDay = DateDerial(Year(Date),Month(Date)+1,1)-1
FirstDay = DateSerial(Year(Date),Month(Date),1)

 

[mcescher]

Subtract one day from the first of next month.

Function FirstOfMonth(dtmYourDate As Date) As Date
Dim intYear As Integer
Dim intMonth As Integer
intYear = Year(DateAdd("m", 1, dtmYourDate))
intMonth = Month(DateAdd("m", 1, dtmYourDate))
FirstOfMonth = DateAdd("d", -1, DateSerial(intYear, intMonth, 1))
End Function

 

[Douglas J Steele]

Or, more simply,

Function FirstOfMonth(dtmYourDate As Date) As Date
FirstOfMonth = DateSerial(Year(dtmYourDate), Month(dtmYourDate), 1)
End Function

Function EndOfMonth(dtmYourDate As Date) As Date
EndOfMonth = DateSerial(Year(dtmYourDate), Month(dtmYourDate) + 1, 0)
End Function

 

[mcescher]

I had not seen the zero technique before, that's so cool.

Thanks a bunch.

Chris

Or, more simply,

Function FirstOfMonth(dtmYourDate As Date) As Date
FirstOfMonth = DateSerial(Year(dtmYourDate), Month(dtmYourDate), 1)
End Function

Function EndOfMonth(dtmYourDate As Date) As Date
EndOfMonth = DateSerial(Year(dtmYourDate), Month(dtmYourDate) + 1, 0)
End Function

 

[Douglas J Steele]

DateSerial is very understanding...

Try DateSerial(2005, 17, 40) and see what you get.