Last Day of the month
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 -- JT |
Last Day of the month
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 |
Last Day of the month
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. JT wrote: 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 -- JT -- Dave Peterson |
Last Day of the month
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... -- HTH... Jim Thomlinson "JT" wrote: 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 -- JT |
Last Day of the month
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 -- Gary''s Student - gsnu200830 "JT" wrote: 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 -- JT |
Last Day of the month
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 |
Last Day of the month
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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200830 "JT" wrote: 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 -- JT |
All times are GMT +1. The time now is 11:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com