Tie a Calendar week to a Scoped Projected Week

[jodi.sage]

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!

 

[Gary Brown]

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
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

 

[jodi.sage]

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]

Glad I could help.
I DO recommend the...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
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.