ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Spreading date range across days in weeks (advanced) (https://www.excelbanter.com/excel-discussion-misc-queries/256779-spreading-date-range-across-days-weeks-advanced.html)

StephenT

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

Spreading date range across days in weeks (advanced)
 
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 employees 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


:)[_2_]

Spreading date range across days in weeks (advanced)
 
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$3D1,D1-1,$B$3))
Cell D3 =DAYS360(IF($A$3$C$1,$A$3,$C$1)-1,IF($B$3E1,E1-1,$B$3))-C3
Cell E3 =DAYS360(IF($A$3$C$1,$A$3,$C$1)-1,IF($B$3F1,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 employees 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

Spreading date range across days in weeks (advanced)
 
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$3D1,D1-1,$B$3))
Cell D3 =DAYS360(IF($A$3$C$1,$A$3,$C$1)-1,IF($B$3E1,E1-1,$B$3))-C3
Cell E3 =DAYS360(IF($A$3$C$1,$A$3,$C$1)-1,IF($B$3F1,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 employees 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

Spreading date range across days in weeks (advanced)
 
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$3D1,D1-1,$B$3))
Cell D3 =DAYS360(IF($A$3$C$1,$A$3,$C$1)-1,IF($B$3E1,E1-1,$B$3))-C3
Cell E3 =DAYS360(IF($A$3$C$1,$A$3,$C$1)-1,IF($B$3F1,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 employees 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


Bob I

Spreading date range across days in weeks (advanced)
 
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.

"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$3D1,D1-1,$B$3))
Cell D3 =DAYS360(IF($A$3$C$1,$A$3,$C$1)-1,IF($B$3E1,E1-1,$B$3))-C3
Cell E3 =DAYS360(IF($A$3$C$1,$A$3,$C$1)-1,IF($B$3F1,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 employees 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



Teethless mama

Spreading date range across days in weeks (advanced)
 
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 employees 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


Fred Smith[_4_]

Spreading date range across days in weeks (advanced)
 
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" wrote in message
...
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$3D1,D1-1,$B$3))
Cell D3 =DAYS360(IF($A$3$C$1,$A$3,$C$1)-1,IF($B$3E1,E1-1,$B$3))-C3
Cell E3 =DAYS360(IF($A$3$C$1,$A$3,$C$1)-1,IF($B$3F1,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 employees 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




All times are GMT +1. The time now is 11:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com