Complicated date math help

[woodycanuck]

I have a challenging problem involving date math. For context, this data has to do with calculating a criminal defendant's prior convictions to figure out sentencing. The task is, for each prior conviction, figure out how many days it was before the current offense date. But we need to exclude any time the person was violating probation. Then count how many of those old offenses are less than 2 years old after the excluded time.

Here is a sample. "Start date" shows 3 prior conviction dates, "End Date" is the current offense date, and there are two spans of time that must be excluded from the calculation. Notice that sometimes the exclusion period overlaps an old offense date, so of course we only exclude the portion after the old offense date (it could also extend past the current offense date, same idea). I need to be able to do this for any number of priors, and any set of excluded date ranges. Is it possible to do this in a formula to answer the question: For an offense date of X, how many countable priors does this person have?

HelpMeDodger.xlsx
	B	C	D	E	F	G
22	Start Dates		End Date		Exclude from	Exclude to
23	1/01/15		1/01/18		6/01/16	7/01/16
24	1/01/16				1/01/14	6/01/15
25	1/01/17
Sheet1

 

[bstory84]

So here is my take on your challenge.
1. Is there an "End Date"[Column D]. If so then proceed with calculation, If not then no result.
2. Determine Overlap Days to exclude.
--If Exclude From>=Start Date & Exclude to <= End Date then Exclude to - Exclude From = Days to exclude
----In the scenario where first statement is false evaluate 1 more time for partial overlap
--If Exclude to >=Start Date then Exclude to - Start date = Days to exclude.
3. "End Date" - "Start Date" - Excluded days (overlap) = Days counted

See if this helps.

=(D2-B2)-IF(AND(F2>=B2,G2<=D2),G2-F2+1,IF(G2>=B2,G2-B2+1,0))

 

[Eric W]

Here's another take:

Book1
	A	B	C	D	E	F	G	H	I
22		Start Dates	Include	End Date		Exclude from	Exclude to		Priors
23		1/1/2015	FALSE	1/1/2018		6/1/2016	7/1/2016		2
24		1/1/2016	TRUE			1/1/2014	6/1/2015
25		1/1/2017	TRUE
26			TRUE
27			TRUE
28			TRUE
29			TRUE
30			TRUE
Sheet11

Cell Formulas
Range	Formula
I23	I23	=SUMPRODUCT(--(D23-B23:B30<732),--C23:C30)
C23:C30	C23	=MIN((B23-F$23:F$30)*(B23-G$23:G$30))>=0

I added the C column to decide if the date in column B falls within any of the ranges in F23:G30. I probably could have included it in the final formula, but it would have been pretty messy. Then the final formula in I23 is pretty simple. Only 2 conditions, if the date is within 732 days of the date in D23, and if the date should be included by using the C column.

Depending on what version of Excel you have, you may need to confirm the C23 formula with Control+Shift+Enter, or this might work:

=AGGREGATE(15,6,(B23-F$23:F$30)*(B23-G$23:G$30),1)>=0

If you could update your profile with the version of Excel you're using, it would help us know what functions we can suggest.



Hope this helps!

 

[woodycanuck]

Thanks for the ideas guys, these are really helpful! I'm going to sit down over the weekend and try to sort the whole thing out.