slraymond

Trouble with weeks elapsed/remaining per quarter calculation.

Discussion created by slraymond on Jan 1, 2013
Latest reply on Jan 1, 2013 by slraymond

I have these fields:

 

z_WeeksElapsed

z_WeeksRemaining

 

They calculate the number of weeks elapsed in a quarter, whose start date is set by the user in the field QTRStartDate.

 

They have been functioning properly during our fiscal Q1, which ended on 12/29/12; but with the new quarter being placed last year, on Sunday Dec. 30 2012, z_WeeksElapsed = (-52) and z_WeeksRemaining = 65.

 

Here are the calculations:

 

z_WeeksElapsed =

Let (

[

isToday = Get ( CurrentDate ) ;

dayNumEndFiscalQ = DayOfYear ( Date ( Month ( Resources::QTRStartDate ) + 3 ; 0 ; Year ( Resources::QTRStartDate )));

dayNumStart = DayOfYear (Resources::QTRStartDate);

dayNumToday = DayOfYear (isToday);

daysInFiscalQ = dayNumEndFiscalQ - dayNumStart + 1;

daysElapsed = dayNumToday - dayNumStart;

weeksElapsed = Int (daysElapsed/7)

];

weeksElapsed

)

 

z_WeeksRemaining =

13-z_WeeksElapsed

 

Can anyone recommend a fix?

Outcomes