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



        • 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




          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



            Thanks a lot!


            This works great


            Best regards