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

[MK_QSL]

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);

[Gysbert_Wassenaar · Accepted solution]

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

[MK_QSL]

I think you have updated your reply later on which I missed to check.

This is the one which I was looking for.. Though I managed to do it other way which was very complex to understand.
This code is really simple to understand and giving the same output.

Thanks Gysbert..