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. |
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) |