What's Wrong With this Date Calculation?

Discussion created by slraymond on Jun 4, 2012
Latest reply on Jun 4, 2012 by comment

I have four fields, StartingDate, CurrentWeek, WeeksElapsed, and WeeksRemaining, which enable me to display the number of weeks elapsed this fiscal quarter and the number remaining.

StartingDate = [auto-enter data, developer manually sets date in field options. In this solution, StartingDate is 4/1/2012]

CurrentWeek = ( Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 1 ) & "..." & ( Get ( CurrentDate ) + 7 - DayOfWeek ( Get ( CurrentDate ) ) )

WeeksElapsed =

Let ( [ shift = 2 - DayOfWeek ( StartingDate ) ;

d1 = StartingDate + shift ;

d2a = Get(CurrentDate) - ( DayOfWeek ( Get(CurrentDate) ) = 7 ) - 2 * ( DayOfWeek ( Get(CurrentDate) ) = 1 ) ;

d2 = d2a + shift

] ;

Div ( d2 - d1 ; 7 ) + (Mod ( d2 - d1 ; 7 ) ≥ 2 )

) // let

WeeksRemaining = 13-z_WeeksElapsed

Apparently, my week counts are flipping on each Tuesday before the expected Sunday. So right now, for example, on Monday, 6/4, I see "week 9, 4 remaining," which is accurate. But tomorrow, I expect to see "week 10, 3 remaining," 5 days early.

Where did I go wrong?