Skip to main content
J
JodyKo
New Participant
November 28, 2016
Question

Week number calculation

  • November 28, 2016
  • 6 replies
  • 1759 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

    GrahamJarrett
    GrahamJarrett
    New Participant
    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
      New Participant
      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
      New Participant
      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)

        William--
        William--
        New Participant
        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
        M
        MollieSh
        New Participant
        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
        M
        MollieSh
        New Participant
        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
        A
        AdinaPi1
        New Participant
        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.
        Doug_Den_Hoed__AtAppStore
        Doug_Den_Hoed__AtAppStore
        New Participant
        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
        Terms & ConditionsAccessibility statement

        Loading footer...