| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tie a Calendar week to a Scoped Projected Week
Hi All!
Hoping someone can help me with this problem. What I want to do is define a Scoped Project Week which won't or doesn't necessarily follow a calendar week. Our qa projects are of short duration, 6 to 10 weeks, so our week 1 could be any week in a calendar year. This is part of an Excel template the entire team will use, the data is exported data from Access (which I know very little about). The weeks are needed to chart inflow/outflow of defects for a dashboard summary. EX: Col A B G H Start Date End Date Start Proj Week End Proj Week 9/1/2006 9/10/2006 Week1 Week2 9/13/2006 9/20/2006 Week2 Week3 9/20/2006 9/20/2006 Week3 Week3 9/30/2006 10/16/2006 Week4 Week6 Using the function from Chip P. web site (TY Chip) I managed to get this far, but am limited by the 7 nested If statements: =IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)<=36,"Week1") - For some reason I have to start with this function in Cell G2 to start., not sure why. =IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=36,"Week1",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=37,"Week2",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=38,"Week3",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=39,"Week4",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=40,"Week5"))))) =IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=41,"Week6",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=42,"Week7",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=43,"Week8",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=44,"Week9",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=45,"Week10"))))) Is there an easier way to write it without using nested IF statements to cover the entire 10 to 12 week project? Thank you in advance! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Tie a Calendar week to a Scoped Projected Week
Gary,
Thanks much! Greatly appreciate your quick response, including pointing out my mistake. Your formula is so much cleaner and works perfectly! Regards, Jodi Gary Brown wrote: First, 'Week 4' in col G calculates out to 'Week 5' and 'Week 6' in Col H should be 'Week 7'. Second, using the table below, the formulas should be... G2 - ="Week " & ROUNDUP((A2-$A$2+0.000005)/7,0) H2 - ="Week " & ROUNDUP((B2-$A$2+0.000005)/7,0) Row Col A B G H 1 Start Date End Date Start Proj End Proj 2 09/01/2006 09/10/2006 Week1 Week2 3 09/13/2006 09/20/2006 Week2 Week3 4 09/20/2006 09/20/2006 Week3 Week3 5 09/30/2006 10/16/2006 Week5 Week7 Copy the formulas in G2 and H2 down. - The reason for the '+0.000005' is ONLY because without it the very first formula (and ONLY that 1st formula) would calculate out to 'Week 0' instead of 'Week 1'. If you hard code cell G2 to say 'Week 1', you can get rid of the '+0.000005' in the formula. -- HTH, Gary Brown If this post was helpful to you, please select ''YES'' at the bottom of the post. " wrote: Hi All! Hoping someone can help me with this problem. What I want to do is define a Scoped Project Week which won't or doesn't necessarily follow a calendar week. Our qa projects are of short duration, 6 to 10 weeks, so our week 1 could be any week in a calendar year. This is part of an Excel template the entire team will use, the data is exported data from Access (which I know very little about). The weeks are needed to chart inflow/outflow of defects for a dashboard summary. EX: Col A B G H Start Date End Date Start Proj Week End Proj Week 9/1/2006 9/10/2006 Week1 Week2 9/13/2006 9/20/2006 Week2 Week3 9/20/2006 9/20/2006 Week3 Week3 9/30/2006 10/16/2006 Week4 Week6 Using the function from Chip P. web site (TY Chip) I managed to get this far, but am limited by the 7 nested If statements: =IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)<=36,"Week1") - For some reason I have to start with this function in Cell G2 to start., not sure why. =IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=36,"Week1",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=37,"Week2",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=38,"Week3",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=39,"Week4",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=40,"Week5"))))) =IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=41,"Week6",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=42,"Week7",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=43,"Week8",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=44,"Week9",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=45,"Week10"))))) Is there an easier way to write it without using nested IF statements to cover the entire 10 to 12 week project? Thank you in advance! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Tie a Calendar week to a Scoped Projected Week
Glad I could help.
I DO recommend the... If you hard code cell G2 to say 'Week 1', you can get rid of the '+0.000005' in the formula. approach as it will negate problems that MIGHT arise from having EXACTLY an amount that will tip the scales to the next week because of the '+0.000005'. Good Luck, -- Gary Brown If this post was helpful to you, please select ''YES'' at the bottom of the post. " wrote: Gary, Thanks much! Greatly appreciate your quick response, including pointing out my mistake. Your formula is so much cleaner and works perfectly! Regards, Jodi Gary Brown wrote: First, 'Week 4' in col G calculates out to 'Week 5' and 'Week 6' in Col H should be 'Week 7'. Second, using the table below, the formulas should be... G2 - ="Week " & ROUNDUP((A2-$A$2+0.000005)/7,0) H2 - ="Week " & ROUNDUP((B2-$A$2+0.000005)/7,0) Row Col A B G H 1 Start Date End Date Start Proj End Proj 2 09/01/2006 09/10/2006 Week1 Week2 3 09/13/2006 09/20/2006 Week2 Week3 4 09/20/2006 09/20/2006 Week3 Week3 5 09/30/2006 10/16/2006 Week5 Week7 Copy the formulas in G2 and H2 down. - The reason for the '+0.000005' is ONLY because without it the very first formula (and ONLY that 1st formula) would calculate out to 'Week 0' instead of 'Week 1'. If you hard code cell G2 to say 'Week 1', you can get rid of the '+0.000005' in the formula. -- HTH, Gary Brown If this post was helpful to you, please select ''YES'' at the bottom of the post. " wrote: Hi All! Hoping someone can help me with this problem. What I want to do is define a Scoped Project Week which won't or doesn't necessarily follow a calendar week. Our qa projects are of short duration, 6 to 10 weeks, so our week 1 could be any week in a calendar year. This is part of an Excel template the entire team will use, the data is exported data from Access (which I know very little about). The weeks are needed to chart inflow/outflow of defects for a dashboard summary. EX: Col A B G H Start Date End Date Start Proj Week End Proj Week 9/1/2006 9/10/2006 Week1 Week2 9/13/2006 9/20/2006 Week2 Week3 9/20/2006 9/20/2006 Week3 Week3 9/30/2006 10/16/2006 Week4 Week6 Using the function from Chip P. web site (TY Chip) I managed to get this far, but am limited by the 7 nested If statements: =IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)<=36,"Week1") - For some reason I have to start with this function in Cell G2 to start., not sure why. =IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=36,"Week1",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=37,"Week2",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=38,"Week3",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=39,"Week4",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=40,"Week5"))))) =IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=41,"Week6",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=42,"Week7",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=43,"Week8",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=44,"Week9",IF(TRUNC(((Start_Date-DATE(YEAR(Start_Date),1,0))+6)/7)=45,"Week10"))))) Is there an easier way to write it without using nested IF statements to cover the entire 10 to 12 week project? Thank you in advance! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to copy cells to rows below | Excel Discussion (Misc queries) | |||
| Macro to insert copied cells | Excel Discussion (Misc queries) | |||
| Calendar Template with week numbers | Excel Discussion (Misc queries) | |||
| user defined function | Excel Worksheet Functions | |||
| Convert week number into calendar month? | Excel Worksheet Functions | |||
Linear Mode
