6 Replies Latest reply: Nov 24, 2016 11:08 AM by James Hanify RSS

    Weeks in a month

    James Hanify

      Hi all,

       

      I am really struggling with the weeks in the month, it seems to work for the most part, then fails miserably in January 16.

       

      this is what is currently in situ:

       

      if((week(Date) - week(monthstart(Date)) + 1)<1,1,(week(Date) - week(monthstart(Date)) + 1)) as WeekNumberInMonth,

       

      I later found its because the Start of week month is remaining at 53 for the whole of january 2016 because 01/01/16 is week 53 so the maths goes wrong.

       

      I've found out I can fix this by saying :

       

      If (week(monthstart(Date)) >= week(Date)

       

      This is the scenario:

          

      Week StartWeekOfYearWeekNoMonthStartExpected Result
      1st January 1653531
      4th January 161532
      11th January 162533

       

      Then what i would like to do is based on the current configuration

       

      If the above matches then use the biggest number, replace any instances by 0 and +1

       

      Therefore

       

      0+0+1 = 1

      1+0+1 = 2

      2+0+1 = 3

       

      However, I'm not sure how you'd do this?

       

      Many thanks for any assistance.

       

      James