2 Replies Latest reply: Jan 30, 2017 8:28 AM by Manish Kachhia RSS

    First Week Must Start from 1st Monday of Every Year March

    Manish Kachhia

      I have below requirement...

       

      User wants every year 1st week must start from 1st Monday of Every March.

       

      Example:

      For current year 2016/17 1st Monday of March is on 07/03/2016

      Fiscal Year 2016/17 and week 1 = 07/03/2016 to 13/03/2016

      Fiscal Year 2016/17 and week 2 = 14/03/2016 to 20/03/2016

      Fiscal Year 2016/17 and week 3 = 21/03/2016 to 27/03/2016

      and so on until last week.....

      Fiscal Year 2016/17 and week 52 = 27/02/2017 to 05/03/2017

      Logic is for next year 2017/18 first Monday of March is on 06/03/2017

      so Last Week must run until 05/03/2017

       

       

      For next year 2017/18 1st Monday of March is on 06/03/2017

      Fiscal Year 2017/18 and week 1 = 06/03/2017 to 12/03/2017

      Fiscal Year 2017/18 and week 2 = 13/03/2017 to 19/03/2017

      Fiscal Year 2017/18 and week 3 = 20/03/2017 to 26/03/2017

      and so on until last week.....

      Fiscal Year 2017/18 and week 52 = 26/02/2018 to 04/03/2018

      Logic is for next year 2018/19 first Monday of March is on 05/03/2018

      so Last Week must run until 04/03/2018

       

      Hope this is clear...

       

      Sample Script to work

      Let vStartDate = Num(YearStart(Today()));
      Let vEndDate = Num(YearEnd(Today(),5));
      
      
      Calendar:
      Load 
        $(vStartDate) + IterNo() - 1 as TempDate
      AutoGenerate 1
      While ($(vStartDate) + IterNo() - 1) <= $(vEndDate);
      
      
        • Re: First Week Must Start from 1st Monday of Every Year March
          Gysbert Wassenaar

          Yay for excel I say. Create your calendar for the next two decades there and simply load the excel file.

           

          Or something like this:

           

           

           

          For i = 2016 to 2035

           

            Let vFirstDay = MakeDate($(i),3) + Pick(Num(WeekDay(MakeDate($(i), 3)))+1, 0, 6, 5, 4, 3, 2, 1);

            Let vLastDay = MakeDate($(i)+1,3) - 1 + Pick(Num(WeekDay(MakeDate($(i)+1, 3)))+1, 0, 6, 5, 4, 3, 2, 1);

           

            Calendar:

            LOAD

            Date($(vFirstDay) + RecNo() -1 ) as Date,

            Div( RecNo()-1,7)+1 as Week,

            WeekDay($(vFirstDay) + RecNo() -1 ) as Weekday,

            $(i) as Year

            AutoGenerate

            $(vLastDay) - $(vFirstDay) + 1

            ;

           

          Next