MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

Spreading date range across days in weeks (advanced)

• Follow

```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

```
 0
Reply Utf 2/19/2010 9:07:01 AM

```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
>
```
 0
Reply Utf 2/19/2010 9:14:01 AM

```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
> >
```
 0
Reply Utf 2/19/2010 9:38:01 AM

```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
> > >
```
 0
Reply Utf 2/19/2010 9:59:02 AM

```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
> > > >
```
 0
Reply Utf 2/19/2010 1:20:05 PM

```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
>>>>>
>>>>>

```
 0
Reply Bob 2/19/2010 2:11:21 PM

```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
>
```
 0
Reply Utf 2/19/2010 3:24:01 PM

```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
>> > > >

```
 0
Reply Fred 2/19/2010 3:28:58 PM

7 Replies
258 Views

(page loaded in 0.13 seconds)

Similiar Articles:

7/7/2012 2:20:18 AM