Overlapping Date ranges

Forgive me but I have hijacked someone else question from another forum

It defines what I'd like to do

It works in the main exception pt ii & v

The attached Excel sheet should be self-explanatory I hope!

I have a fixed date range, "F" (A3:B3) and a number of variable date ranges ("V") in columns D and E.

I want column F to return the number of days between D and E that fall within range F All 6 possible examples are listed-

(i) V starts and ends before F = 0 days
(ii) V starts before range F and ends within F = some of F (start F - end V)
(iii) V starts before before F and ends after F = all of F
(iv) V starts within F and ends within F = some of F (end V - Start V)
(v) V starts within F and ends after F = some of F (start V - end F +1)
(vi) V starts after F and ends after F = 0 days


I've tried 3 different scenarios, all came up with the same answer

.=MAX(0,MIN(B$3,E3)-MAX(A$3,D3)+1)
.=MAX(MIN($B$3,E3)-MAX(D3,$A$3)+1,0)
.=IF(OR($B$3<D3,$A$3>E3),0,(MIN($B$3,E3)-MAX($A$3,D3)+1))

(I think the latter is possibly my best bet but I'm unable to tweak it the correct way)

All help greatly appreciated

Thanks Richard & zbor for the quick responses I've tweaked my sheet so to represent a clearer explanation of what im trying to achieve..

The sheet represents a payment schedule, & in this case 31 days in the month

If I use examples ii & v it gives me a total of 32 days

In example ii I'm trying to achieve 4 days instead of 5 & in iv 25 instead of 26

The tweaking is in ii & v as explained above

I'v also manually input in col G what im expecting

Hope this ia little clearer

pvd

Muchly appreciated Richard

By way of explanation as i said its a series of payments schedules

I'll use the example of 30/6/15 to 15/10/15
Date diff is 107 days because you count the first day but not the last in the payment schedule. Schedule is on a monthly basis

If it were to be split (because a change of rate)

Jun=1
Jul=31
Aug=31
Sep=30
Oct=14 (15-1)
And i have many to work thru , all with varying start/end date

Because the all encompassing formula in excel usually requires +1 (& it works in a majority of instances, as demonstrated on the sheet) I needed a fix that made an allowance for a finish date in the month that didn't require the +1 & although I'm not a complete newbie I could get my head around solving this... You have & I'm very very grateful

pvd

Richard apologies for the delay. Tested at home & so far so good

Appreciate the followup

G'day Dave. Looks good but as I'm on an iPad I report back when in front of a desktop

Thanks for the response!