Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel to make the days cary over month to month automaticly | New Users to Excel | |||
Excel 2003 month to month data change grid | Excel Discussion (Misc queries) | |||
Retrieve data for previous 3, 6, 12 month given current month | Excel Worksheet Functions | |||
copy worksheet from previous month and rename to current month | Excel Programming | |||
transfer cell $ amount to other sheet month-to-month without overc | Excel Discussion (Misc queries) |