# Calculate how many days in one date range fall into a second date range

1. ## Calculate how many days in one date range fall into a second date range

Hi,

I am working on a summary sheet for my seasonal property rental business, and am trying to summarise 10 years worth of data from the previous owners to help me with my own projections.

I have a vast list of previous contracts with Start Dates, and End Dates for each, as well as a large list of rentals during that time.

I need to know during each contract period (for example 1-Jan-2003 => 31-Mar-2011), how many of these days were high season, low season & holiday season. (for example holiday season : 22-Dec => 5-Jan).
If all it took was to calculate how many years, and how many days in a high-season, this would be easy, but using the numbers included above, only 5 days out of the 14 day high season range actually fall into the managed period.

I have been working on so many other summaries, that I have been able to figure out, that this one is starting to give me brain-cramps.

Thanks in advance for any help.  Register To Reply

2. ## Re: Calculate how many days in one date range fall into a second date range

Isn't that 15 days if you include start and end date? So for your example that would be 8 years @ 15 days and one year with 5 days = 125. This formula will give you that result

=SUMPRODUCT((TEXT(ROW(INDIRECT(A2&":"&B2)),"mdd")+0>=1222)+(TEXT(ROW(INDIRECT(A2&":"&B2)),"mdd")+0<=105))

assuming start date in A2 and end date in B2

How is low season defined?  Register To Reply

3. ## Re: Calculate how many days in one date range fall into a second date range

Great.

I see what you did, and can adapt this to the remaining categories. There is no real point putting the other season's here, as they are different for different properties. Hence the fun I am having with all of this. :D  Register To Reply