none
Week number in custom text field RRS feed

  • Question

  • Hi,

    I used this formula to show the weeknumber in a custom text field:

    "W" & Format(DateAdd("d";7-Weekday([Finish]);[Finish]);"ww")

    For 2015 it works fine, but the first week of 2016 is displayed as week 2 instead of 1. The problem may come from the fact that 2015 had 53 weeks instead of 52. Is there any way to solve this?

    Monday, March 7, 2016 8:22 AM

Answers

  • Hi Peperbusse,

    Your issue simply comes down to the week numbering convention that is used in the calculation, and your formula uses default parameters (i.e. all weeks start on Sunday, and the week containing 1Jan is W1).  You need to add two optional parameters to the "Format" function in your formula:

    "W" & Format(DateAdd("d";7-Weekday([Finish]);[Finish]);"ww";2;2)

    The first "2" assigns Monday as the first day of the week (1 is Sunday), and the next "2" assigns W1 to the week that has at least 4 days in the year.  I think that is the ISO/European standard.

    You can adjust these parameters according to this link:

    https://msdn.microsoft.com/en-us/library/office/gg251755.aspx

    good luck, tom

    Monday, March 7, 2016 2:17 PM

All replies

  • Hello Peperbusse,

    Formula is working perfectly, but instead of semi-colon you should give commas. Format(DateAdd("d",7-Weekday([Finish]),[Finish]),"ww").

    Catch could be if your that tasks is not 1 day duration for 1st Jan 2016 then it will not give 1 as the value, instead flow on to next week and give 2; as 1st Jan 2016 was on Friday (only working day of the New Year in-case of Standard settings). Please check the finish date for that task.

    Hope this helps!!


    Sapna Shukla

    Monday, March 7, 2016 10:25 AM
    Moderator
  • Hi Sapna,

    Tnx for your reply. Please mind that the only difference between semi-colon and comma is the language pack you're using. When I use a comma, MSP auto-corrects it to semi-colon.

    That said, the formula does work but returns the wrong week numbers for 2016. I have only milestones with zero duration, so task is starting and finishing on same day. All weeks are displayed one more than the timeline above the Gantt tells.


    • Edited by Peperbusse Monday, March 7, 2016 10:39 AM
    Monday, March 7, 2016 10:39 AM
  • Hi Peperbusse,

    Your issue simply comes down to the week numbering convention that is used in the calculation, and your formula uses default parameters (i.e. all weeks start on Sunday, and the week containing 1Jan is W1).  You need to add two optional parameters to the "Format" function in your formula:

    "W" & Format(DateAdd("d";7-Weekday([Finish]);[Finish]);"ww";2;2)

    The first "2" assigns Monday as the first day of the week (1 is Sunday), and the next "2" assigns W1 to the week that has at least 4 days in the year.  I think that is the ISO/European standard.

    You can adjust these parameters according to this link:

    https://msdn.microsoft.com/en-us/library/office/gg251755.aspx

    good luck, tom

    Monday, March 7, 2016 2:17 PM
  • Tom, you're the man! Works like a charm. Thanks a lot.
    Monday, March 7, 2016 3:07 PM
  • Hi,

    I used this formula to show the weeknumber in a custom text field:

    "W" & Format(DateAdd("d";7-Weekday([Finish]);[Finish]);"ww")

    For 2015 it works fine, but the first week of 2016 is displayed as week 2 instead of 1. The problem may come from the fact that 2015 had 53 weeks instead of 52. Is there any way to solve this?

    Peperbusse,

    Please see the article here at: http://www.msptips.com/Formulas/WN.html 

    I tried to explain the whole story on week numbering. 

    Regards.


    Ismet Kocaman | Learn Formulas | Articles | Connect

    Tuesday, March 8, 2016 12:24 AM