4 Replies Latest reply on Aug 30, 2018 10:29 PM by Sankarmagesh Rajan

    Custom Week

    sneha simha

      Dear Team

                              Sorry if its a repeated one. I need a calculation of weeks in such a way that 1) Week will start from Monday to Sunday (achieved by changing the date properties).

                                                                                                                                                      2) If Friday falls on 1st of any month, then the first week will have only 3 days like Friday,Saturday and Sunday.The second week should start from Monday in my example Date of Monday will be 4th say August .I have data from April2017 till date.

      In case 1st falls on a Saturday then WEEK 1 will have only 2days and so on.



      Please help me.




      Sneha Simha

        • 1. Re: Custom Week
          Naveen B

          Hi Sneha,


          Is it possible to attach some sample data and along with desired result,


          You can take superstore and provide sample desired O/P how you are expecting so that it will be easy to decipher the issue




          • 2. Re: Custom Week
            Mahfooj Khan

            If you set the date properties then you can achieve this. Could you please explain your exact requirement and expected output with some screenshot? If possible then provide some sample mock up data in packaged workbook.


            1 of 1 people found this helpful
            • 3. Re: Custom Week
              sneha simha

              Hi Naveen


                                 Attached the Sample store data and in the sheet 2 , u can see how i want my tabulation.


              I want to show the weeknumbers and the sales value along with the days.Here i want to know one thing. week 1 to week 52 is for a full year.I want to show my data from say August2017 till date.Is there a possibility that i can start my week from week 1 to week 4 only for August2018.

              If yes please reply m using this formula to get my weeks


              ROUND((DATEADD('day', 3, DATETRUNC('week',[Date]))-

              DATEADD('day', 3, DATETRUNC('year', DATEADD('day', 3, DATETRUNC('week',[Date])))) +

              DATEPART('weekday', DATEADD('day', 2, DATETRUNC('year', DATETRUNC('week',[Date]))))

              -4) / 7 + 1,0)

              • 4. Re: Custom Week
                Sankarmagesh Rajan

                hi Sneha,


                Yes. I understand this situation. Every month you want show your week as 1,2,3,4 etc with dynamic

                try this


                if MONTH([Date])=month(today()) then

                DATEPART('week',[Date]) - DATEPART('week',DATETRUNC('month',[Date]))+1





                1 of 1 people found this helpful