Thread
:
Simple question - help please and thank you.
View Single Post
#
4
Posted to microsoft.public.excel.programming
Tom Ogilvy
external usenet poster
Posts: 27,285
Simple question - help please and thank you.
DateSerial(year(dt),Month(dt)+1,0)
gives the last day of the month for Date
demo'd from the immediate windows (date in m/d/yyyy format)
dt = DateSerial(2007,5,1) ' May 1, 2007
? dt
5/1/2007
? dateserial(year(dt),month(dt)+1,0)
5/31/2007
? day(dateserial(year(dt),month(dt)+1,0))
31
--
Regards,
Tom Ogilvy
"Tokyo or bust" <Tokyo or
wrote in message
...
Hey,
I am calculating simple monthly returns on a series of daily asset prices
for a number of years worth of data. The problem is I am not sure how to
indentify the end of a month, and the start of the next in a column of
05/01/2006 format sequential dates. I could just add another column and
use
the "month" function in excel (what I have done thus far), but I have to
do
the whole operation in VBA. What I have now is:
If ActiveSheet.Cells(dayofmonth, 1) = ActiveSheet.Cells(dayofmonth - 1, 1)
Then day +1 and continue till they are different etc......
The first column is a single number derived from a = month(date column)
formula
This unfortunately will not cut it.
I want to be able to do the same operation, but not have to use the month
function in excel - just reference the date column (04/09/2006 format). I
am
pretty sure I need to use a ActiveCell.Formula and a month function, but
am
unsure as to how to write the formula - any help would be tremendously
appreciated.
Regardless of how easy it is, I have sweat over this for some time.......
Thanks - cheers.
Reply With Quote
Tom Ogilvy
View Public Profile
Find all posts by Tom Ogilvy