Formula & Dates

[Guest]

Hi there,

Is it possible to set the number of rows in a range based on a start and end date?. I'm not sure even how to start this, but if say I has the start date in cell A1 and end date in cell B1, it is then possible to set how many rows will be programatically generated relating to number of month's between the two dates in any defined range. My first row would be made of a series of formula which I would like to be copied down to the end of the range, which would generate a set of figures.

Any help appreciated !

Thanks for your help

John

 

[Jake Marx]

Hi John,

I'm not sure if this is what you're looking for, but here's a function that
will return the number of months between any 2 dates.

Public Function glGetNumberOfRows(dtStart As Date, _
dtEnd As Date) As Long
glGetNumberOfRows = DateDiff("m", dtStart, dtEnd)
End Function

You could call this function from code:

MsgBox glGetNumberOfRows(Sheet1.Range("A1").Value, _
Sheet1.Range("A2").Value)

Or from a worksheet:

=glGetNumberOfRows(A1, A2)

If you're going to use it from a worksheet, you could simply use the
built-in DATEDIF worksheet function (not documented in help in some
versions). You can learn more about it on Chip Pearson's site:

http://www.cpearson.com/excel/datedif.htm

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]