Find-out Days Overlaped [Excel Formula Homework]

Few days back we have learned how to find if 2 sets of dates overlap using Excel Formulas. For eg. we have learned that to check whether {1 July,2010 to 23 July 2010} overlaps with the date range {15 July, 2010 to 16 August 2010}, we can use a formula like,

=if(or(and(x>=a,x<=b),and(a>=x,a<=y)), "Overlap","Do not overlap")

where {x,y} are {1 July,2010 to 23 July 2010} and {a,b} are {15 July, 2010 to 16 August 2010}.

Robert, an Excel School student asked a question last week that adds an interesting twist to this problem.

I’m trying to calculate days using several nested IF functions, but I keep getting errors – was hoping you might be able to evaluate my formula and tell me what I’m doing incorrectly:

Here is my framework – I have a start date field which can be any date (let’s call this f6), and an end date field which can be any date (let’s call this g6). I want to calculate the number of days within the start date and end date that fall within the month May 2010.

Essentially Robert wants to find out how may days between {x,y} overlap with {a,b}.

Now, I have already figured out a simple enough solution for it. But it is no fun showing off what I know. So here is the homework for you.

Your homework:

Figure out how to find the number of days in overlap and post your answer in comments (click here).

PS: Today, I am attending a talk by Seth Godin at Hyderabad. I am super excited to finally hear him live. I have been reading his blog for almost 5 years now, and he has been one of the motivating forces for me. So naturally I will be slow to approve comments or reply to mails. Give me a day to get back to you.