Historical Aging Report

**Indy** · 09-17-2008, 11:42 AM

Hi,

I need to find a way to historically track aging of open issues (30, 60, 90, 120 days old). I have thousands of rows of data that include the open date, closed date, and a days old column (calculated as of TODAY for all currently open issues). I need to be able to fill a table with how many issues were open over 30, 60, 90, and 120 days for every week ending date since the beginning of the year.

Assume I have column A as the identifier, B as the Open Date, C as the Closed Date, and D as the Age.

I have a system that works for calculating this real-time (how many for each bucket as of right now), but I really need to be able to fill in each bucket for previous dates. I know nothing about VBA, so if there is a way to do this without VBA, that would be very helpful.

Thanks in advance for any help.

**Indy** · 09-17-2008, 02:19 PM

Here is a sample file with the two tabs of data to help illustrate what I need the 2nd tab to display for me. I forgot to mention that I first have to check on the status of the issue by checking the date in the Closed Date column and using today's date if that column=0.

**Indy** · 09-19-2008, 10:53 AM

OK, so I think I almost have it, but then I ran into another problem. What I was trying to us is this:

Aging!E2=COUNTIFS(Open_Date,CONCATENATE("<=",A2),Closed_Date,CONCATENATE(">=",A2),{A2-Date_Closed},">=120")

Closed_Date is the range in Data tab for all of the Closed Dates, and Open_Date is the same of the Open Dates.

Does anyone know how I can dynamically check every field that meets the previous criteria for open and closed dates and then subtract the closed date from the date I am checking in Column 1 of the Aging sheet, and then count those times when that result is >= 120, or between two numbers (>=60, <90)?