How many days overlap?

**whycali2** · 11-15-2009, 03:34 PM

How can I calculate how many days overlap date ranges?

I'm working out complex child custody arrangements, and need to calculate how many days each year the child is in mom's custody and how many days in dad's.

Custody alternates weeks - starting Monday (week 1=dad, week 2=mom, etc.). I've figured out how to determine if a single day falls on dad's or mom's week:

Please Login or Register  to view this content.

There are a bunch of holidays which override the regular alternating weeks. For example, with dad on Christmas Eve and Day in Even years (e.g., with dad 12/24/2010 - 12/25/2010).

So, to calculate the total days, I need to figure out how many days of each holiday falls on the OTHER parents regular week? i.e., how many days in the range 12/24/2010 through 12/25/2010 (dad's year) fall on Mom's regular week? if it all falls on dad's regular week, he gains no days. But if one or two days fall on mom's regular week, dad will gain a day or two that year. I'll have to do this calculation for every holiday.

Given a start date, an end date and custodial parent, how can I calculate the number of days taken from the other parent in Excel 2007?

Thank you!

**darkyam** · 11-15-2009, 05:16 PM

You'll have to test it (and be sure to confirm the formula in B10 when you adjust the ranges with Ctrl+Shift+Enter), but I *think* this works.

**martindwilson** · 11-15-2009, 05:26 PM

this is a good solution to a very unecessary problem!

**whycali2** · 11-15-2009, 06:22 PM

Okay, I bite. Why is it unnecessary?

**martindwilson** · 11-15-2009, 06:26 PM

its probably deemed frivolous to comment, but if 2 people worry that that see their child x % less than the other or the law has to do it for them . then the world is in a very sad state.
well thats my rant! but you did ask.

**whycali2** · 11-15-2009, 09:48 PM

I hear you.... and wish the laws didn't make this so important. But, it is what it is.

**whycali2** · 11-15-2009, 09:56 PM

I'm a bit confused by this reply.

I think I may have asked for a bit more than I really need. I want to provide a date range (holiday), and the parent who has custody during that holiday. Then have the formula (whatever it is) tell me how it affected the dad's custodial days. How many days did he gain or lose?

Here's an example, weeks alternate and start/end on Monday. Let's say dad's custody week is 12/21/2009 - 12/27/2009. Since they alternate, mom has the week before and week after. Here are some examples, with what I would need the result to be:

START - 12/21/2009
END - 12/22/2009
PARENT - Mom
RESULT = -2 (dad lost 2 days of custody 12/21 & 12/22)

START - 12/20/2009
END - 12/22/2009
PARENT - DAD
RESULT = +1 (dad gained one day, 12/20/2009)

START - 12/20/2009
END - 12/23/2009
PARENT - Mom
RESULT = -3 (dad lost 3 days of custody 12/21, 12/22 & 12/23)

START 12/18/2009
END 12/20/2009
PARENT - Mom
RESULT = 0 (dad loses no days because 12/18 - 12/20 were already mom's days)

Any ideas on how to create this formula?

Thanks again, and sorry for being vague.