Spreading date range across days in weeks (advanced)

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

Wow, that didn't display so well. Here's an image of the worksheet http://i50.tinypic.com/2s79myr.jpg "StephenT" wrote: > 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 >

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" wrote: > Wow, that didn't display so well. Here's an image of the worksheet > http://i50.tinypic.com/2s79myr.jpg > > "StephenT" wrote: > > > 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 > >

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? ":)" wrote: > 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" wrote: > > > Wow, that didn't display so well. Here's an image of the worksheet > > http://i50.tinypic.com/2s79myr.jpg > > > > "StephenT" wrote: > > > > > 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 > > >

Anyone? Don't tell me I've stumped the famed Excel discussion group... This'll be a first. "StephenT" wrote: > 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? > > ":)" wrote: > > > 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" wrote: > > > > > Wow, that didn't display so well. Here's an image of the worksheet > > > http://i50.tinypic.com/2s79myr.jpg > > > > > > "StephenT" wrote: > > > > > > > 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 > > > >

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. StephenT wrote: > Anyone? Don't tell me I've stumped the famed Excel discussion group... = > This'll be a first. >=20 > "StephenT" wrote: >=20 >=20 >>Thanks :), but unfortunately this doesn't work if the start date is not= in=20 >>the first week. This logic may be modified but I fear we will end up wh= ere I=20 >>have been the last 24 hours and wrestling with a heinous nested IF=20 >>statement...=20 >> >>Any other suggestions?=20 >> >>":)" wrote: >> >> >>>Based on your example, I have this but you need to try other dates to = see if=20 >>>it is robust enough to use. >>> >>>Cell C3 =3DDAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>D1,D1-1,$B$3)) >>>Cell D3 =3DDAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>E1,E1-1,$B$3))-C3= >>>Cell E3 =3DDAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>F1,F1-1,$B$3))-D3= -C3 >>>Cell F3 =3DDAYS360(A3-1,B3)-SUM(C3:E3) >>> >>>"StephenT" wrote: >>> >>> >>>>Wow, that didn't display so well. Here's an image of the worksheet=20 >>>>http://i50.tinypic.com/2s79myr.jpg >>>> >>>>"StephenT" wrote: >>>> >>>> >>>>>Hello=20 >>>>> >>>>>This particular problem has been causing me some consternation, and = I was=20 >>>>>wondering if anyone has already solved this problem or has a creativ= e=20 >>>>>solution=20 >>>>> >>>>> >>>>>I have a date range, and I would like to convert that into how many = days in=20 >>>>>each week correspond to that range.=20 >>>>> >>>>> >>>>>Example=20 >>>>>Consider a range of an employee=E2=80=99s working dates. Lets say I = have two input=20 >>>>>fields: Start date (A3) and end date (B3), and want to be able to co= unt the=20 >>>>>number of days in each week C3: F3. Consider March of 2010, so Week = 1=20 >>>>>commences on 01/03/2010, Week 2 commences on 08/03/2010, etc. The st= arting=20 >>>>>date of each week is in C1:F1 >>>>> >>>>>A3 and B3 are inputs, the formula is required for C3:F3. They work w= eekends,=20 >>>>>so ignore the working day effect. >>>>> >>>>> >>>>> A B C = D =20 >>>>> E F = =20 >>>>> =20 >>>>>1 01/03/2010 =20 >>>>>08/03/2010 15/03/2010 22/03/2010 >>>>>2 Start Date End Date Week1 Week2 = =20 >>>>> Week3 Week4 =20 >>>>>3 05/03/2010 17/03/2010 3 {days in week) 7 {days in week= } 3=20 >>>>>{days in week} 0 {days in week} =20 >>>>> >>>>>As you can see, in this input range there is 3 days in Week 1 (5th t= o 7th=20 >>>>>inc.) and 7 days in Week 2(8th to 14th), 3 days in=20 >>>>> >>>>>Please, no macros. Thanks in advance >>>>>

Try this: =SUMPRODUCT(--(WEEKNUM(ROW(INDIRECT($A3&":"&$B3)),2)=WEEKNUM(C$1,2))) "StephenT" wrote: > 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 >

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. "StephenT" <StephenT@discussions.microsoft.com> wrote in message news:8CC824FF-F47B-4C9D-84C3-1255E5A3F6FD@microsoft.com... > Anyone? Don't tell me I've stumped the famed Excel discussion group... > This'll be a first. > > "StephenT" wrote: > >> 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? >> >> ":)" wrote: >> >> > 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" wrote: >> > >> > > Wow, that didn't display so well. Here's an image of the worksheet >> > > http://i50.tinypic.com/2s79myr.jpg >> > > >> > > "StephenT" wrote: >> > > >> > > > 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 >> > > >

Similiar Articles:

Domain certificate error - microsoft.public.windows.server.active ...
Have a Nice day. Jorge Silva MVP Directory Services ... Windows-CertificateServicesClient-AutoEnrollment Date ... > You've been wrestling with this for over two weeks now.

Calculator: Add to or subtract from a date
The Date Calculator calculates the duration between 2 dates. It adds or subtracts days, weeks, months and years from a given date.

Create a date range using excel formula | Get Digital Help ...
My template needs to have the date formatted across one of ... Column I: DATE 17-Jun-11 Column J: Date range (7 days after ... Read this post: Date ranges: Weeks within a month

How to Calculate Range Spread | eHow.com
Range spread is a ... Date Range. At one time or another, we all have needed to figure the number of days, weeks, or ... Spread Duration. Spread duration is an advanced bond ...

Date Duration Calculator: Days between two dates - timeanddate.com
... calculates the difference in number of days ... Also see the advanced duration calculator which ... Specify time. Include end date in calculation (1 day is added)

Automatic Rolling Months in Excel, Dynamic Rolling Months in Excel ...
Spread some love, It makes you awesome! ... and copy this formula across range (c5:m5) =INDEX ... what the current month is and return the date for the first day ...

7/7/2012 2:20:18 AM