I'm trying to create a calendar field in my script to change the week numbers so that week 1 starts on the first Monday in September.
So, 03/09/2012 is usually week 36, but I want it to be week 1.
Similarly, 31/12/2012 is the first day of week 1 in my existing calendar, but I would want this date to be in week 18 for my new week field.
I thought I had done what I needed to do by using the MakeDate function in my script. This seems to work perfectly up until 07/01/2013, when suddenly the week numbers on my new week field changed to be Sunday starting weeks (rather than Monday starting as they were up until that point).
Weeks are working perfect but I am facing problem in scripting years properly. I am incrementing year with the script above (logic: for first 13 quarters increment calender year by 1 to get fiscal year and after that use calender year as fiscal year). This does not apply to certain exceptions like 12/31/2012 (attached screenshot) as its in fiscal week 14 and calender year is still 2011 not 2012.
My question is how to handle years when starting fiscal calender different than calender year?
Thanks for helping with this. I very much appreciate it.