design question: array of elapsed days per month

[KR]

I am looping through a large number (<1000) records in my macro to tally up
other information. I just realized that I need to collect another type of
information, and I'm struggling with the best approach/methodology to
capture the information I need.

Each record includes a start date, and some include an end date. My report
needs to include a table that shows for each month, the age of each open
record for that month, in days (buckets of 0-10, 11-20, 21-30, 31-40, 41-50,
51-60, and 60+). The assumption is that the report reflects the status as of
the 1st of each month (so I need to take into account the number of days in
each month).

As a general example, if I only had one record, and it was open starting Jan
15:

J F M A M J J A S O N D
0-10 0 0 0 0 0 0 0 0 0 0 0 0
11-20 0 1 0 0 0 0 0 0 0 0 0 0
21-30 0 0 0 0 0 0 0 0 0 0 0 0
31-40 0 0 1 0 0 0 0 0 0 0 0 0
41-50 0 0 0 0 0 0 0 0 0 0 0 0
51-60 0 0 0 0 0 0 0 0 0 0 0 0
60+ 0 0 0 1 0 0 0 0 0 0 0 0

(Feb1 = ~15 days old, Mar 1 = ~45 days old, April 1 = ~75days old, but for
my report exact days matter)

I'm currently thinking that if I take the start date, and the end date (or
now() if there is no end date), and cycle through each month for each
record, checking the elapsed time, and using a select case statement for
each month to see which bucket to increment.

However, I'm not sure how to take into account the different number of days
in each month, to make sure I have accurate elapsed times as of the first
day of each month.

Any suggestions?

Many thanks,
Keith

 

[KR]

The best option I have come up with so far is to start with:

For i = 1 to 2000 'number of records
ItemStartDate = Sheet1.Range("A" & str(i)).value
For p = 1 to 1460 '(started a maximum of four years ago, 4 x 365 days)
if (day(ItemStartDate + p) = 1 then
if (ItemStartDate+ p) > EndDate or (ItemStartDate+ p) > now() then exit
for
Select Case (ItemStartDate + p)
Case <11
'increment my count for that month
Case 11 to 20
(etc.)
endif
next p
next i

Is there an easier way to loop through the first days of each month, so I
can use that date to evaluate durations? I'm thinking the first day of each
month for 4 years = 48 calculation loops, vs. 1460 if I have to keep
checking each day to see if it is the first day of a month....

Thanks,
Keith
XL2003/ Win2000