11 Replies Latest reply on Mar 30, 2007 11:25 AM by jdeline

    Date Range Within a Date Range

    Wizard950
      I'm suffering from a mental block on this one and I'm hoping someone here can help. What I need to do is determine how many date ranges are between a broader date range. For example a school year runs from 9/1 to 6/30 or 180 work days. What I need to do is compute the number of 9/1 to 6/30 periods between say 1/1/2007 and 9/30/2009. The result of this find is used in othere calculations in a rather complex report. Any help is appreciated.
        • 1. Re: Date Range Within a Date Range
          jdeline Level 1
          So in your example, you have 2 ranges, correct? 9/1/2007 - 6/30/2008 and 9/1/2008 - 6/30/3009.
          • 2. Re: Date Range Within a Date Range
            Level 7
            jdeline wrote:
            > So in your example, you have 2 ranges, correct? 9/1/2007 - 6/30/2008 and 9/1/2008 - 6/30/3009.

            No, I believe the OP is asking to find out how many times the date range
            9/1/?? to 6/30/?? occurs between the dates of 1/1/2007 and 9/40/2009.

            To clarify the entire first date range must be contained inside of the
            second, correct. You don't care about partial overlaps and the
            beginning or the end?


            • 3. Date Range Within a Date Range
              Wizard950 Level 1
              The years in the 9/1 to 6/30 range are irrelevant, I think. They can be any year. What I'm looking for is how many sets of September 1 through June 30 occur in a larger date range like January 1, 2007 and September 30, 2009. In this example there is a partial set at 1/1/2007 through 6/30/2007. Two full sets between 9/1/2007 and 6/30/2008 and 9/1/2008 through 6/30/2009. Finally there is a partial set 9/1/2009 through 9/30/2009. I hope that explains what I'm trying to do.
              • 4. Re: Date Range Within a Date Range
                Wizard950 Level 1
                Ian and Jdeline see my message posted @ 4:24, they must have crossed in the mail. I hope that explains the problem better.
                • 5. Date Range Within a Date Range
                  jdeline Level 1
                  So it seems, if you are looking for full sets, there are 2 in your example? Or do partial sets count as well? If so, you have 4?
                  • 6. Re: Date Range Within a Date Range
                    Wizard950 Level 1
                    Partial sets count. In my example there are 2 full sets and two partial sets. The correct result of the process would be 2.7

                    The two partials add up to 7 months out of the 10 in a full set.
                    • 7. Re: Date Range Within a Date Range
                      jdeline Level 1
                      You want to count months in each set and normalize it to a fraction of 9 months. A full set would normalize to 1.0, a partial set with 3 months would normalize to 0.3. The attached code should give you an idea of how to approach this problem.
                      • 8. Date Range Within a Date Range
                        Dan Bracuk Level 5
                        if (ListFind("1,2,3,4,5,6,9,10,11,12", month(startdate) gt 0)
                        periods = 1;
                        else
                        periods = 0;

                        ThisDate = DateAdd("m",1, StartDate);
                        while (ThisDate lte EndDate) {
                        if month(ThisDate) is 9) // was 4, oops, sorry
                        periods = periods + 1;
                        ThisDate = DateAdd("m",1, ThisDate);
                        }
                        • 9. Re: Date Range Within a Date Range
                          scooter5791 Level 1
                          Yet another method...

                          <cfset start_date = DateFormat('01/01/2007', 'mm/dd/yyyy')>
                          <cfset end_date = DateFormat('09/30/2009', 'mm/dd/yyyy')>
                          <cfset start_year = DatePart('yyyy', start_date)>
                          <cfset end_year = DatePart('yyyy', end_date)>
                          <cfset schoolyear_start = '09/01/'>
                          <cfset schoolyear_end = '06/30/'>

                          <cfset count = 0>
                          <cfloop index="rec" from="#start_year#" to="#end_year#">
                          <cfset tmp_start = DateFormat('#schoolyear_start##rec#', 'mm/dd/yyyy')>
                          <cfset tmp_end = DateFormat('#schoolyear_end##rec + 1#', 'mm/dd/yyyy')>
                          <cfif DateCompare(tmp_start,start_date) gt -1 and DateCompare(tmp_end, end_date) eq -1>
                          <cfset count = count + 1>
                          </cfif>
                          </cfloop>

                          <cfoutput>
                          <br>There are #count# school year periods between #start_date# and #end_date#
                          </cfoutput>
                          • 10. Re: Date Range Within a Date Range
                            scooter5791 Level 1
                            Sorry, that last post doesn't take the patials into account - I didn't see that post until afterward - but it would still be pretty easy
                            • 11. Re: Date Range Within a Date Range
                              jdeline Level 1
                              My earlier code post had a couple of problems. First, there are 10 months in a full set, not 9 as I indicated. Also, the DateDiff( ) function returns one less than the desired number of months. I have corrected these issues in the code below.