Excel - VBA - is day the last day of a calendar month?


Is there any easy way to find out if a date is the last day of a calendar month in VBA?

For example, 30 October 2012 is not a last day but 31 October 2012 is a last day

Many thanks in advance
Who is Participating?
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
If DateSerial(Year(MyDate), Month(MyDate) + 1, 0) = MyDate Then
        MsgBox "Date is the last day of the month."
    End If

pony10usConnect With a Mentor Commented:
You can find it by taking the current month and incrementing it by 1 then decrement the day by 1.  Here is a sample taken from Microsoft's MSDN site.

Function dhLastDayInMonth(Optional dtmDate As Date = 0) As Date
    ' Return the last day in the specified month.
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    dhLastDayInMonth = DateSerial(Year(dtmDate), _
     Month(dtmDate) + 1, 0)
End Function

Open in new window

barry houdiniConnect With a Mentor Commented:
If you want to know whether a date is the last of the month just check whether the next day is the 1st

something like

If Day(MyDate + 1) = 1 Then.....

regards, barry
alisonthomAuthor Commented:
Thank you so much! all of you
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.

All Courses

From novice to tech pro — start learning today.