Weeks in period
 

Hi,

Is there anyway that I can take two different overlapping periods and figure
out how many weeks/days of one fall into another?
--
I need so much help, but right now, some Excel help will suffice!!

Weeks in period
 

Need more info. Post a sample and the expected result.

--
Biff
Microsoft Excel MVP


"Jose" wrote in message
...
Hi,

Is there anyway that I can take two different overlapping periods and
figure
out how many weeks/days of one fall into another?
--
I need so much help, but right now, some Excel help will suffice!!

Weeks in period
 

On Jan 24, 12:38*pm, Jose wrote:
Is there anyway that I can take two different overlapping periods
and figure out how many weeks/days of one fall into another?

Suppose the start and end date of one period are in A1 and A2
respectively, and the start and end date of the other period are in B1
and B2.

The number of days of in the overlap, if any, is:

=max(0, min(A2,B2) - max(A1,B1) + 1)

The number of "weeks" is harder. First, you need to define the term.

If you simply mean the number of 7-day intervals, take the computed
days (above) and divide by 7, rounding or rounding up or rounding down
as you desire.

If you mean the number of calendar weeks, that's a whole other
ballgame. The following might work for you:

=(max(A1,B1)<=min(A2,B2)) * max(0, weeknum(min(A2,B2)) -
weeknum(max(A1,B1)) + 52*(year(min(A2,B2)) - year(max(A1,B1))) + 1)

That assumes that you want to count any partial week as entire week.
Obviously, it would be more efficient to compute the overlapping start
and end dates -- max(A1,B1) and min(A2,B2) respectively -- in cells.

Caveat: I have not tested the above formulas with all combinations
overlaps (or not) and spreads across multiple years. You should test
them carefully to be sure that they truly work -- unless someone
provides something better, of course.

HTH.