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




      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.