Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
design question: array of elapsed days per month
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 -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
design question: array of elapsed days per month
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 "KR" wrote in message ... 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 -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't get NETWORKDAYS to calc elapsed banking days in a month | Excel Worksheet Functions | |||
Solve 1st of month after specified number of days elapsed | Excel Worksheet Functions | |||
Please Help With Days Elapsed And Days Remaining Calculation | Excel Worksheet Functions | |||
Elapsed time in days | Excel Worksheet Functions | |||
Month and Days question | Excel Discussion (Misc queries) |