Improve company productivity with a Business Account.Sign Up

x
?
Solved

Excel - calculating overlapping period of two date ranges

Posted on 2013-11-17
4
Medium Priority
?
4,864 Views
Last Modified: 2016-09-29
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
Comment
Question by:tiziano456
4 Comments
 
LVL 50

Accepted Solution

by:
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

by:tiziano456
ID: 39655667
so simple) - thank you
0
 

Expert Comment

by:xenium
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

by:C H
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question