+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    02-02-2010
    Location
    playa del carmen, mexico
    MS-Off Ver
    ms office 2007
    Posts
    52

    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.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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?
    Audere est facere

  3. #3
    Registered User
    Join Date
    02-02-2010
    Location
    playa del carmen, mexico
    MS-Off Ver
    ms office 2007
    Posts
    52

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

    Great.

    Thanks daddylonglegs.

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1