Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i convert a given # of days to Weeks, and days | Excel Discussion (Misc queries) | |||
Advanced Filter when using Date Range Cells | Excel Discussion (Misc queries) | |||
Advanced filter a dynamic date range | Excel Worksheet Functions | |||
A number of days into weeks and days | Excel Worksheet Functions | |||
How do I sort by date (not days, weeks, months) in Excel 2000? | Excel Discussion (Misc queries) |