Last Day of the month

[JT]

I have a date in a cell and want to find out what the last day for the month
is. Is there an easy way to do this with VB and just include it with the
rest of the code?

Thanks for the help

 

[egun]

I usually do it something like this (VBA might not be exactly right):

Sub test()
Dim i As Integer
'
i = Last_Day_of_Month(2, 2009) ' Last day of Feb, 2009?
MsgBox "Last day of Feb, 2009 = " & i
'
End Sub

Function Last_Day_of_Month(theMonth As Integer, theYear As Integer) As Integer
Dim date1 As Date, date2 As Date
'
date1 = DateSerial(theYear, theMonth, 1) ' First day of input month/year
date2 = DateAdd("m", 1, date1) ' Add a month to get first day of next
month
Last_Day_of_Month = Day(DateAdd("d", -1, date2)) ' Then subtract one
day to get last day of input month/year
'
End Function
'

HTH,

Eric

 

[Dave Peterson]

Dim myDate As Date
Dim myLastDay As Long
Dim myLastDate As Date

myDate = DateSerial(2009, 2, 1) 'or Date

myLastDay = Day(DateSerial(Year(myDate), Month(myDate) + 1, 0))
myLastDate = DateSerial(Year(myDate), Month(myDate) + 1, 0)
MsgBox myLastDay & vbLf & myLastDate

===
I'm not sure if you wanted to see 28 or Feb 28, 2009.

 

[Jim Thomlinson]

Here is some code to give you the end of the month...

Public Function EndOfMonth(ByVal dte As Date) As Date
EndOfMonth = DateSerial(Year(dte), Month(dte) + 1, 0)
End Function

Sub test()
MsgBox EndOfMonth(Range("A1").Value)
End Sub

Run the test subprocedure whcih takes the value in Cell A1 of the active
sheet and gives you the last day of the month. You can also use the formula
=DATE(YEAR(A1), MONTH(A1)+1, 0)
directly in a Cell to get the end of the month. finally there is a function
in the analysis toolpack called EOMonth but if your user does not have it
installed it returns a #Name error...

 

[Gary''s Student]

Say the date is in cell A1. In another cell enter:

=DATE(YEAR(A1),MONTH(A1)+1,0)


To do the same thing with VBA:

Sub lastday()
Dim d As Date
d = Evaluate("DATE(YEAR(A1),MONTH(A1)+1,0)")
MsgBox (d)
End Sub

 

[egun]

That's a neat trick - putting in zero for the day. I never knew you could do
that! A more elegant solution, for sure!

Eric

 

[Curt]

You are close to my problem. What I am trying to do is if (Day Is 15) do this
I do not care what month or year are only the day. If day=15 b3=19.96
Looking thru and seen your entry so decided to throw out my problem
Thanks