6 Replies Latest reply: Apr 2, 2012 8:36 AM by Anne Duffy RSS

    Week format as weekending date

      Hi Guys

       

      as part of my script I have the dates formatted as

       

      Date([Date received]) as Date,

      Month(Date([Date received])) as Month,

      Week(Date([Date received])) as Week,

      Year(Date([Date received])) as Year,

       

      However rather than the Week format be as 1,2,3, as in week 1 week 2 week 3 etc

       

      Is it possible to have it formatted as the last date of the weeks, so if the 1/1/2012 was a monday

       

      Week 1 = 7/1/2012

      Week 2 = 14/1/2012

       

      Etc etc

       

      Thanks a mill
      A

        • Week format as weekending date
          Stefan Wühl

          I think you want to have a look at the weekend() function:

           

          WeekEnd(date [, shift = 0 [,weekoffset = 0]])

          Returns a value corresponding to a timestamp with the last millisecond of the last date ( Sunday ) of the calendar week containing date The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the week which contains date. Negative values in shift indicate preceding weeks and positive values indicate succeeding weeks. If you want to work with weeks not starting midnight between Sunday and Monday, indicate an offset in days in weekoffset. This may be given as a real number indicating days and/or fractions of a day.

          Examples:

          weekend ( '2006-01-12' ) returns '2006-01-15' with an underlying numeric value corresponding to '2006-01-15 23:59:59.999'

          weekend ( '2006-01-12', -1 ) returns '2006-01-08' with an underlying numeric value corresponding to '2006-01-08 23:59:59.999'

          weekend ( '2006-01-12', 0, 1 ) returns '2006-01-16' with an underlying numeric value corresponding to '2006-01-16 23:59:59.999'

          • Week format as weekending date
            Jason Michaelides

            From the F1 help:

             

            WeekEnd( date [, shift = 0 [,weekoffset = 0]] )

            Returns a value corresponding to a timestamp with the last millisecond of the last date ( Sunday ) of the calendar week containing date The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the week which contains date. Negative values in shift indicate preceding weeks and positive values indicate succeeding weeks. If you want to work with weeks not starting midnight between Sunday and Monday, you may indicate an offset in days in weekoffset. This may be given as a real number indicating days and/or fractions of a day.

            Example:

            weekend ( '2006-01-12' ) returns '2006-01-15' with an underlying numeric value corresponding to '2006-01-15 23:59:59.999'

            weekend ( '2006-01-12', -1 ) returns '2006-01-08' with an underlying numeric value corresponding to '2006-01-08 23:59:59.999'

            weekend ( '2006-01-12', 0, 1 ) returns '2006-01-16' with an underlying numeric value corresponding to '2006-01-16 23:59:59.999'

             

             

            Hope this helps,

             

            Jason

            • Week format as weekending date
              Celambarasan Adhimulam

              Hi,

                   Try with WeekEnd function

                   like WeekEnd(Date)

               

              celambarasan

              • Week format as weekending date
                jagan mohan rao appala

                Hi,

                 

                Try using WeekEnd() to get weekend date of the given date

                 

                WeekEnd([Date received]) as Week

                 

                Hope this helps you.

                 

                Regards,

                Jagan.