5 Replies Latest reply on Aug 1, 2012 10:50 AM by KylePutzier

    Need a week number formula

    KylePutzier

      I need a week number formula that is similar to WeekOfYear() except it doesn't reset on Jan 1.

      The number it generates is arbitrary. It just needs to calculate the next number in it's sequence every 7 days.

       

      Kyle

        • 1. Re: Need a week number formula
          beverly

          Kyle, did you know that you can auto-enter a serial number and specify the increment to be something other than "1"?

          Start with any number you want, and increment by "7".

          HTH,

          Beverly

           

          I need a week number formula that is similar to WeekOfYear() except it doesn't reset on Jan 1.

          The number it generates is arbitrary. It just needs to calculate the next number in it's sequence every 7 days.

          Kyle

           

          • 2. Re: Need a week number formula
            comment

            KylePutzier wrote:

             

            I need a week number formula that is similar to WeekOfYear() except it doesn't reset on Jan 1.

             

            Your request is not quite clear. What is the input for this calculation (assuming it's a calculation)? Knowing the purpose would  be useful, too.

            • 3. Re: Need a week number formula
              KylePutzier

              I'm making a time clock type of program. I will have a number of records containing timestamp fields and need to group/summarize them by a week (Mon-Sun). The week calculation would be based on one of these timestamps. The normal WeekOfYear function works perfectly, except it will fail when a week spans a new year.

               

              Kyle

              • 4. Re: Need a week number formula
                comment

                KylePutzier wrote:

                 

                need to group/summarize them by a week (Mon-Sun).

                 

                IMHO, it would be best to use a calculation that returns the date of the first day of the week (this is useful for labeling the group).

                 

                 

                For a week starting on Monday, try =

                 

                Let ( [

                d = GetAsDate ( TimestampField )

                ] ;

                d - Mod ( d - 1 ; 7 )

                )

                • 5. Re: Need a week number formula
                  KylePutzier

                  Michael,

                   

                  That will do.

                   

                  Thank you.

                   

                  Kyle