Calculate age for last day of each month??

[codonnell]

Great help from everyone on the site – and I hope you might be able to help me with this one …

I have three columns of dates ‘Open Date’ and ‘Closed Date’ and ‘Age’ from a one year sample of data (April ’06-April ‘07). Some records will have an ‘Open Date’ and a ‘Closed Date’ that fall within the same month and year – others will not.

I want to generate a report that will tell me:

- How many records remained ‘open’ on the last day of each month – accounting for records that remained ‘open’ going into the month and remain ‘open’ at end of month.

- What the average age of all ‘open’ records on the last day of each month.

Example:

Record has an ‘open date’ of January 31, 2007, a ‘closed date’ of March 5, 2007, and an ‘Age’ of 33 days. I would need excel to know that the record remained open on the last day of January and February and tell me the age of the record for these two last days of the month.

Another consideration is that some of the records will not have a ‘closed date’ at all – since they have remained ‘open’ since being created. These also need to be included on the last day of each month.

On a larger scale – I’d like to apply this on all records and get a count of the number of records that remained ‘open’ on the last day of each month and what the average age of all open records was.

I’d hope my output is similar to one column of months, another column identifying the number of records ‘open’ at end of that month, and another column detailing the age of all open issues at the end of that month.

I’ve attached a sample and would appreciate if someone could demonstrate this for me!

Christopher

[daddylonglegs]

Here's my take on it, does this work for you?

[DCSwearingen]

Here's my take on it, does this work for you?

For years I have been using the EOMONTH function to do this sort of thing. I have found out the hard way that most people do not have Analysis ToolPak loaded and therefore get errors. Half of those don't even know how to load an addin...

For those people, I simply copy and paste values into the sheets I send them.

I did not know that using zero for the day value in the DATE function would return the last date of the previous month.

Now I can just change my formulas from EOMONTH to DATE and not have to worry about Analysis ToolPak...

A big THANK YOU, yet again to all of the wonderful people who offer their help on this board.