x
Solved

# Excel - calculating overlapping period of two date ranges

Posted on 2013-11-17
Medium Priority
4,864 Views
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
(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
(v) V starts within F and ends after F = some of F
(vi) V starts after F and ends after F = 0 days

Just would like the elegant way to calculate this without using a nested IF for all 6 permutations

Thanks!
overlapping-date-ranges.xlsx
0
Question by:tiziano456

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 39654922
You can use this formula in F3 copied down

=MAX(0,MIN(B\$3,E3)-MAX(A\$3,D3))

see attached

regards, barry
overlapping-date-ranges-barry.xlsx
0

Author Comment

ID: 39655667
so simple) - thank you
0

Expert Comment

ID: 41364635
Follow-up: is there a way to provide the total (192) as a single cell arrayformula? Yes.. see this link:

http://www.experts-exchange.com/questions/28897581/calculating-overlapping-period-of-two-date-ranges-arrayformula.html
0

Expert Comment

ID: 41821206
Hello,

I was wondering how this formula might be modified to return the number of days within a date range for different individuals, where each individual has a different number of entries?
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.