Spreading date range across days in weeks (advanced)

[StephenT]

Hello

This particular problem has been causing me some consternation, and I was
wondering if anyone has already solved this problem or has a creative
solution


I have a date range, and I would like to convert that into how many days in
each week correspond to that range.


Example
Consider a range of an employee’s working dates. Lets say I have two input
fields: Start date (A3) and end date (B3), and want to be able to count the
number of days in each week C3: F3. Consider March of 2010, so Week 1
commences on 01/03/2010, Week 2 commences on 08/03/2010, etc. The starting
date of each week is in C1:F1

A3 and B3 are inputs, the formula is required for C3:F3. They work weekends,
so ignore the working day effect.


A B C D
E F

1 01/03/2010
08/03/2010 15/03/2010 22/03/2010
2 Start Date End Date Week1 Week2
Week3 Week4
3 05/03/2010 17/03/2010 3 {days in week) 7 {days in week} 3
{days in week} 0 {days in week}

As you can see, in this input range there is 3 days in Week 1 (5th to 7th
inc.) and 7 days in Week 2(8th to 14th), 3 days in

Please, no macros. Thanks in advance

 

[StephenT]

Wow, that didn't display so well. Here's an image of the worksheet
http://i50.tinypic.com/2s79myr.jpg

 

[:)]

Based on your example, I have this but you need to try other dates to see if
it is robust enough to use.

Cell C3 =DAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>D1,D1-1,$B$3))
Cell D3 =DAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>E1,E1-1,$B$3))-C3
Cell E3 =DAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>F1,F1-1,$B$3))-D3-C3
Cell F3 =DAYS360(A3-1,B3)-SUM(C3:E3)

 

[StephenT]

Thanks , but unfortunately this doesn't work if the start date is not in
the first week. This logic may be modified but I fear we will end up where I
have been the last 24 hours and wrestling with a heinous nested IF
statement...

Any other suggestions?

 

[StephenT]

Anyone? Don't tell me I've stumped the famed Excel discussion group...
This'll be a first.

 

[Bob I]

You might want to wait a day or two for people to see your problem. This
is a newsgoup that people from around the globe read.

 

[Teethless mama]

Try this:

=SUMPRODUCT(--(WEEKNUM(ROW(INDIRECT($A3&":"&$B3)),2)=WEEKNUM(C$1,2)))

 

[Fred Smith]

Try this:
=MAX(0,MIN(7-MAX(0,$A3-C$1),$B3-MAX(C$1,$A3)+1))
and copy to the other cells.

Regards,
Fred
PS. Never use DAYS360 unless you really want 30 days in every month.