Fiscal Weeks - Week 1 in September

[danielle_v]

Hi All,

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

Any help greatly appreciated.

Thanks,

Danielle

[#2 · Accepted solution]

Right(WeekName(TempDate,0,-36),2) as NewWeek

then your week 36 will start by 1 i think

[danielle_v]

Many thanks, this has achieved exactly what I need

[#4]

Very useful discussion. I am leveraging this to create a fiscal calender that starts in october (starts on saturday for the week whose monday is first monday of October).

I am using below script to adjust the weeks. So fiscal week 1 in 2012 starts on Sat, 1st Oct 2011 and goes on.

right (WeekName(TempDate,13,-2),2) as FiscalWeek

if(Right(WeekName(TempDate,13,-2),2)>0 and Right(WeekName(TempDate,13,-2),2)<=13,YearName(TempDate,1),YearName(TempDate,0)) as FiscalYear,

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.