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.

[Guest]

So in your example, you have 2 ranges, correct? 9/1/2007 - 6/30/2008 and 9/1/2008 - 6/30/3009.

[Wizard950]

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.

[Newsgroup_User]

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?

[Wizard950]

Ian and Jdeline see my message posted @ 4:24, they must have crossed in the mail. I hope that explains the problem better.

[Guest]

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?

[Wizard950]

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.

[Guest]

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.

[Guest]

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.

[Dan_Bracuk]

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);
}

[scooter5791]

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>

[scooter5791]

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