Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,819
Default 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


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,718
Default 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

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,389
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can i convert a given # of days to Weeks, and days eric Excel Discussion (Misc queries) 1 November 20th 09 07:08 PM
Advanced Filter when using Date Range Cells Jim Excel Discussion (Misc queries) 3 January 25th 09 09:53 PM
Advanced filter a dynamic date range oneandoneis2 Excel Worksheet Functions 2 April 6th 06 08:57 AM
A number of days into weeks and days Robert Christie Excel Worksheet Functions 4 August 31st 05 03:23 AM
How do I sort by date (not days, weeks, months) in Excel 2000? Tony Excel Discussion (Misc queries) 1 January 21st 05 04:28 PM


All times are GMT +1. The time now is 03:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"