Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pay back period | Excel Worksheet Functions | |||
Payback Period | Excel Discussion (Misc queries) | |||
Period to Period percentage change? | Excel Discussion (Misc queries) | |||
Input period, but get .+ | Excel Discussion (Misc queries) | |||
calculate weeks from a start date ( not yr weeks) | Excel Worksheet Functions |