Week number calculation | Community
Skip to main content
J
JodyKo
November 28, 2016
Question

Week number calculation

  • November 28, 2016
  • 6 replies
  • 1765 views
Hi all, I'm trying to build a task report which displays both the task planned completion date as well as a calculated week number for that task's planned completion. This would be equivalent to the WEEKNUM function in Excel. I want to display the week number of the planned completion date-- the tricky part is that some of the tasks are not planned to complete until next year, so I (think I) need to look at the year of the planned completion date, calculate the difference between the task date and Jan 1 of that year, and then calculate the week number from that difference. Would be great if there was a DAYOFYEAR function! Can anyone help with a workaround? Surely someone has done this before?
    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

    6 replies

    Doug_Den_Hoed_AtAppStore
    Community Advisor
    Doug_Den_Hoed_AtAppStoreCommunity Advisor
    Community Advisor
    November 30, 2016
    Hi Jody. The closest helper I am aware of is the DAYOFMONTH function, but I'm not sure that helps much. To roll your own DAYOFYEAR, perhaps you could DATEDIFF the date in question against Jan 1 of that date's Year -- something like DATEDIFF(Planned Start Date,DATE(CONCAT(YEAR("0101",Planned Start Date)))). I've not tested it, mind you, but thought it might give you some ideas. Regards, Doug
    A
    AdinaPi1
    Level 9
    November 30, 2016
    Might be an initial pain to setup, but you can do a calculated field on a task form like this: IF(Planned Completion Date < DATE("01/08/2017"), "1", IF(Planned Completion Date < DATE("01/15/2017"), "2", IF(Planned Completion Date < DATE("01/22/2017"), "3", IF(Planned Completion Date < DATE("01/29/2017"), "4" )))) I tried getting greater than > to work, but it gave the wrong results, but the less than worked great.
    M
    MollieSh
    Level 4
    September 7, 2018
    Hi, I unfortunately don't have a solution for you. But I am looking to try and do the same thing, but I want number of weeks from a date set in a custom field. Did you by chance ever figure anything out? Thanks! Mollie Shatek JLL
    M
    MollieSh
    Level 4
    September 7, 2018
    Hi, Just thought I'd share. I think I got a solution to display the Week number a task starts from a Project custom field. CONCAT("Week ",ROUND(((DATEDIFF(Planned Start Date,Project.Contract Date))/7),0)) You could try something like: CONCAT("Week ",ROUND(((DATEDIFF(Planned Completion Date,Project.Planned Start Date))/7),0)) If you want more exact you could take of the ROUND function. Hope that helps! Mollie Mollie Shatek JLL
    William
    Community Advisor
    WilliamCommunity Advisor
    Community Advisor
    September 7, 2018
    Hi Mollie, The solution for the original post is this: ROUND((((DATEDIFF(Planned Completion Date,DATE(CONCAT("1/1/",YEAR(Planned Completion Date))))+1)-(8-DAYOFWEEK(DATE(CONCAT("1/1/",YEAR(Planned Completion Date)))))-DAYOFWEEK(Planned Completion Date))/7)+2,0) I think your solution is not as complicated: CEIL(DATEDIFF(Planned Completion Date,{DE:Custom Field Name})/7) That is the difference in number of days between your custom field date and the task planned completion; divided by 7; then rounded up to the nearest integer. Also, you could calculate the number of whole weeks between a task's planned completion and a custom date on a project form: CEIL(DATEDIFF(Planned Completion Date,Project.{DE:Custom Field Name})/7) William English
    If you like my content, please take a moment to view and vote on my Idea Requests: https://tinyurl.com/4rbpr7hf
    GrahamJarrett
    GrahamJarrett
    Level 4
    December 7, 2023

    To get the actual ISO Week Number the formula is:

    valueexpression=IFIN(DAYOFWEEK(CONCAT("1/1/",YEAR({entryDate}))),1,6,7,CEIL(DIV(DATEDIFF({entryDate},CLEARTIME(CONCAT("1/1/",YEAR({entryDate})))),7)),SUB(CEIL(DIV(DATEDIFF({entryDate}),CLEARTIME(CONCAT("1/1/",YEAR({entryDate}))),7)),1))
    valueformat=HTML

     

    This accounts for Workfront using Sunday as Day 1 of the week.

     

    I used entryDate as my date but you could use whatever date field you wanted in place of this one.

      Seanna_Hdz
      Seanna_Hdz
      Level 2
      March 7, 2024

      Hello @grahamjarrett - I tried your expression but I am getting errors. It tells me that the second DATEDIFF is incorrect because the function must contain 2 values. Please advise. Thanks! 

       

      GrahamJarrett
      GrahamJarrett
      Level 4
      March 7, 2024

      I actually have updated my formula too...

       

      ROUND((((DATEDIFF({entryDate},DATE(CONCAT("1/1/",YEAR({entryDate}))))+1)-(8-DAYOFWEEK(DATE(CONCAT("1/1/",YEAR({entryDate})))))-DAYOFWEEK({entryDate}))/7)+2,0)

        Terms & ConditionsAccessibility statement

        Loading footer...