2 Replies Latest reply on Jun 4, 2012 12:44 PM by comment

    What's Wrong With this Date Calculation?

    slraymond

      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?

        • 1. Re: What's Wrong With this Date Calculation?
          erolst

          Your complication looks (and is) quite complicated. Here's a simpler approach using DayofYear, with some bonus information thrown in.

           

          As inferred from your last paragraph remark, the calculation gives the week which has fully ended, i.e. on Saturday it will 9, 4, and on Sunday 10,3. It should be easily adjustable for other requirements.

           

          let (

             [ 

              startDate = "1.4.2012" ; // sample data

           

              isToday = Get ( CurrentDate ) ; // control data

           

              dayNumEndFiscalQ = DayOfYear ( date ( month ( startDate ) + 3 ; 0 ; year ( startDate ) ) ) ;

           

              dayNumStart = DayOfYear ( startDate ) ;

           

              dayNumToday = DayOfYear ( isToday ) ;

           

              daysInFiscalQ = dayNumEndFiscalQ - dayNumStart + 1 ;

           

              daysElapsed = dayNumToday - dayNumStart ;

           

              weeksElapsed = Int ( daysElapsed / 7 ) ;

           

              weeksRemaining = 13 - weeksElapsed

             ] ;

           

          "Week " & weeksElapsed & ", " & weeksRemaining & " remaining" & " (days elapsed: " & daysElapsed & ", days remaining: " & daysInFiscalQ - daysElapsed

           

          )

          • 2. Re: What's Wrong With this Date Calculation?
            comment

            slraymond wrote:

             

            the number of weeks elapsed this fiscal quarter

             

            You need to define "elapsed week". See the help on WeekOfYear() and WeekOfYearFiscal() functions.

             

             

             

            slraymond wrote:

             

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

             

            Is this ever going to change? If not, it would be more efficient to use either a one-record Preferences table or a global field, instead of replicating the same data in every record.