Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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
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
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..