2 Replies Latest reply on Oct 25, 2016 5:01 AM by Leslie Raillon

    First day of the week of my past full week

    Leslie Raillon

      Hello everyone,

       

      I have been looking for a solution that I really cannot find.

       

      I have a previous full week number formula that works fine:

      [WEEK NUMBER]=datepart('week',dateadd('week',-1,Today()))

       

      Than I do:

      DATEADD('day',1,date([WEEK NUMBER]))

       

      Which gives me 14/02/1900 00:00:00

      BUT I WANT: 16/10/2016

       

      Really I am far...

      I have understood why I think; which is because [WEEK NUMBER] is not for this years previous week at present.

       

      Any tips?

       

      Thanks a lot

       

      Regards

        • 1. Re: First day of the week of my past full week
          Simon Runc

          hi Leslie,

           

          So the problem here is that your [Week Number] is the datepart of week...which is just the week number (say 44, or Week 44). When you then try and use this to create a date it only has the number 42! Dates are stored as numbers, which are then converted to the dates we (as humans) understand...starting from 01/01/1900....so 1st Jan 1900 is the number 1, 2nd Jan the number 2...and so on.

           

          So if your [Week Number] was 44...the calculation you have says; convert the number 44 to a date...which would be 13/02/1900 (day 44 from 1900), and then add one to it (to give the 14/02/1900).

           

          Something like

           

          DATEADD('day',1,dateadd('week',-1,Today()))

           

          would give you a date from the previous week....you may need to substitute the -1, with a calculation on DATEPART('weekday',today()). This returns the weekday number (so Monday = 2, Tuesday = 3....) meaning you can adjust which date is returned depending on which day of the week it is (if for example, you always want to show the date as a weekending value).

           

          Hope that helps and makes sense.

          2 of 2 people found this helpful
          • 2. Re: First day of the week of my past full week
            Leslie Raillon

            Hi

             

            Thanks a lot!

             

            This works great

             

            Best regards

             

            Leslie