Forecasting payables for this year and next

I have the following formula and it works good but it needs an extra tweek:

local NumberVar ThisWeek := datepart("WW",currentdate);
if datepart("ww",{@Billing Date})=ThisWeek  then "Week 1"
else if datepart("ww",{@Billing Date}-7)=ThisWeek  then "Week 2"
else if datepart("ww",{@Billing Date}-14)=ThisWeek  then "Week 3"
else if datepart("ww",{@Billing Date}-21)=ThisWeek  then "Week 4"
else if datepart("ww",{@Billing Date}-28)=ThisWeek  then "Week 5"
else if datepart("ww",{@Billing Date}-35)=ThisWeek  then "Week 6"
else if datepart("ww",{@Billing Date}-42)=ThisWeek  then "Week 7"
else if datepart("ww",{@Billing Date}-49)=ThisWeek then "Week 8"
else if datepart("ww",{@Billing Date}-56)=ThisWeek  then "Week 9"
else if datepart("ww",{@Billing Date}-63)=ThisWeek  then "Week 10"
else if datepart("ww",{@Billing Date}-70)=ThisWeek  then "Week 11"
else if datepart("ww",{@Billing Date}-77)=ThisWeek  then "Week 12"
else if datepart("ww",{@Billing Date}-84)=ThisWeek  then "Week 13"
else if datepart("ww",{@Billing Date}-91)=ThisWeek  then "Week 14"
else if datepart("ww",{@Billing Date}-98)=ThisWeek then "Week 15"
else if datepart("ww",{@Billing Date}-105)=ThisWeek  then "Week 16"

Anything over sixteen weeks shows as future costs.  The problem I'm having is that it's picking nov 2012 as week one and it shouldn't.  Week1 should be showing for Nov 2011.  Can anyone help?
LVL 1
lrollinsIT ManagerAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
A week in the year is the same.  You need to check the year as well.

The ww option of DatePart returns the week number of the date based on that year.  So given today is 24 Oct 2011, the date 52 weeks from now 22 Oct 2012 will return the same week number.

 
local NumberVar ThisWeek := datepart("WW",currentdate);
local NumberVar ThisYear := Year(currentDate);
if datepart("ww",{@Billing Date})=ThisWeek AND Year({@BillingDate}) = ThisYear then "Week 1" 
else if datepart("ww",{@Billing Date}-7)=ThisWeek  AND Year({@BillingDate}-7) = ThisYear  then "Week 2"
else if datepart("ww",{@Billing Date}-14)=ThisWeek  AND Year({@BillingDate}-14) = ThisYear  then "Week 3"
else if datepart("ww",{@Billing Date}-21)=ThisWeek  AND Year({@BillingDate}-21) = ThisYear  then "Week 4"
else if datepart("ww",{@Billing Date}-28)=ThisWeek  AND Year({@BillingDate}-28) = ThisYear  then "Week 5"
else if datepart("ww",{@Billing Date}-35)=ThisWeek  AND Year({@BillingDate}-35) = ThisYear  then "Week 6"
else if datepart("ww",{@Billing Date}-42)=ThisWeek  AND Year({@BillingDate}-42) = ThisYear  then "Week 7"
else if datepart("ww",{@Billing Date}-49)=ThisWeek  AND Year({@BillingDate}-49) = ThisYear then "Week 8"
else if datepart("ww",{@Billing Date}-56)=ThisWeek  AND Year({@BillingDate}-56) = ThisYear  then "Week 9"
else if datepart("ww",{@Billing Date}-63)=ThisWeek  AND Year({@BillingDate}-63) = ThisYear  then "Week 10"
else if datepart("ww",{@Billing Date}-70)=ThisWeek  AND Year({@BillingDate}-70) = ThisYear  then "Week 11"
else if datepart("ww",{@Billing Date}-77)=ThisWeek  AND Year({@BillingDate}-77) = ThisYear  then "Week 12"
else if datepart("ww",{@Billing Date}-84)=ThisWeek  AND Year({@BillingDate}-84) = ThisYear  then "Week 13"
else if datepart("ww",{@Billing Date}-91)=ThisWeek  AND Year({@BillingDate}-91) = ThisYear  then "Week 14"
else if datepart("ww",{@Billing Date}-98)=ThisWeek  AND Year({@BillingDate}-98) = ThisYear then "Week 15"
else if datepart("ww",{@Billing Date}-105)=ThisWeek  AND Year({@BillingDate}-105) = ThisYear  then "Week 16"

Open in new window


I think there might be an easier formula
Something like

 
Local NumberVar DayDiff;
Local NumberVar WeekDiff;
DayDiff := DateDiff('d',CurrentDate,{@BillingDate});
WeekDiff := Day Diff \ 7;
"Week " CStr(WeekDiff + 1,0,"")

Open in new window


mlmcc
0
 
peter57rCommented:
The problem (assuming there is one) is in @Billing Date, not this formula.
0
 
lrollinsIT ManagerAuthor Commented:
This is my BillingDate formula:

cdate(DateAdd ("D",30 , {PURCHASE_ORDER.DESIRED_RECV_DATE}))

It was for 60 days but they just had me change it and it works fine now, but what if I have to change it back to 60 days?

Thanks
0
 
lrollinsIT ManagerAuthor Commented:
Thanks so much!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.