Link to home
Start Free TrialLog in
Avatar of tiziano456
tiziano456Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Excel - calculating overlapping period of two date ranges

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
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tiziano456
tiziano456
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

so simple) - thank you
Avatar of xenium
xenium
Follow-up: is there a way to provide the total (192) as a single cell arrayformula? Yes.. see this link:

https://www.experts-exchange.com/questions/28897581/calculating-overlapping-period-of-two-date-ranges-arrayformula.html
Avatar of C H
C H
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?