Analyse date frequency cross-referencing multiple date ranges

Hi Guys,

First off let me state that this is a MAJOR extension of a previously asked & solved question, so I apologise if this should have been added to the other thread, found here:-

http://www.excelforum.com/excel-gene...63#post2783663

However, I am looking for a tidy solution to help me summarise the data from a company I have recently taken over. As the data I am trying to summarise is a 10-year complete sales record of the previous company, I would prefer not to include the workbook if at all possible, although I do understand that this may not be easy to figure out if I don't, so let me know, and I will try and randomise the real details if I have to.

Basically...

I have a list of almost 3500 property rentals, which (amongst a ton of other information) has a Property Name, Start Date & End Date.
These 3 columns each have a dynamic range, named 'RentalsList', 'RentalStartList' & 'RentalEndList'.

These rentals were spread amongst 187 different properties.

Only 78 of these 187 properties were managed by the previous company, the rest were rented externally.
These 78 properties have a dynamic range, named 'ManagedList'.

I have a Start Date & End Date for the management of each of the 78 managed properties.
These columns have a dynamic range, named 'ManagedStartList' & 'ManagedEndList'.

These 78 properties were occasionally rented outside of the management period. I even have a few examples of rentals that were half-inside & half-outside of the management period. (i.e rental 27-Dec => 5-Jan, management period 1-Jan => 30-Sep)

Rentals are broken up into 3 seasons, Holiday, High & Low, which determines the price the client paid.

Holiday Season runs from 22-Dec => 5-Jan
High Season runs from 6-Jan => 30-Apr
Low Season runs from 1-May => 21-Dec

And now for the question(s)....

I need to be able to calculate several things.

The previous discussion I linked to earlier, solved my first question, which was How many days within the managed period of each property, fell within each season. Well, actually only Holiday Season, but I adapted this to figure out the other seasons.
The next thing I need to be able to calculate, is how many days during each season, in a managed period, were actually rented. This also needs to calculate arrival/departure date as only a half day, not a full day. i.e.:-

Rental : 3-Jan-07 => 10-Jan-07 = 2.5 Holiday & 4.5 High
But if managed period is 1-Dec-06 => 7-Jan-07, the result needs to show as: 2.5 Holiday & 2 High

The final thing I need to calculate, is individual dates. For example 19-Sep. How many 19-Sep's were available to be rented during managed periods, across all properties, and how many of them actually were rented, to give me a %age.

I hope this explanation is clear enough, and I hope someone can actually help me figure this out, as I'm sure the volume of my hair is less than half what it was when I started.

Thanks so much in advance for any help/suggestions you may have.

Here is the core data I discussed earlier, with all unimportant data removed, and house names changed.

Sorry to do this, but 'Bump' once