Skip to main content
SusanBr
Level 2
Apr 25, 2018 12:22 AM UTC
Solved

Is there a way to determine the week number of a date in WorkFront

I am trying to generate a report that includes the planned start date of a task, but also includes the Week Number that corresponds to the Planned Start Date of the task. In Excel, I can use the WEEKNUM function. Is there a way to do this in a WorkFront report column? Thanks! Susan Browning Internet Testing Systems
This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
KathyLe1Accepted solution
Level 4
Apr 26, 2018 1:23 AM UTC
Hi Susan, This isn't the least bit elegant, and the performance would probably be unpleasant in a large report, but if you're desperate, I believe the following will work. In a quick test I did where I exported a decent subset of data and compared the results against those of WEEKNUM, everything matched. displayname=Test textmode=true valueexpression=ROUND((((DATEDIFF({plannedStartDate},DATE(CONCAT("1/1/",YEAR({plannedStartDate}))))+1)-(8-DAYOFWEEK(DATE(CONCAT("1/1/",YEAR({plannedStartDate})))))-DAYOFWEEK({plannedStartDate}))/7)+2,0) valueformat=HTML Kathy
View in thread

7 replies

KathyLe1Accepted solution
Level 4
Apr 26, 2018 1:23 AM UTC
Hi Susan, This isn't the least bit elegant, and the performance would probably be unpleasant in a large report, but if you're desperate, I believe the following will work. In a quick test I did where I exported a decent subset of data and compared the results against those of WEEKNUM, everything matched. displayname=Test textmode=true valueexpression=ROUND((((DATEDIFF({plannedStartDate},DATE(CONCAT("1/1/",YEAR({plannedStartDate}))))+1)-(8-DAYOFWEEK(DATE(CONCAT("1/1/",YEAR({plannedStartDate})))))-DAYOFWEEK({plannedStartDate}))/7)+2,0) valueformat=HTML Kathy
Translate
nsoans1106
Level 2
Apr 20, 2021 10:32 AM UTC

This worked for me like a charm @Kathy Leeman‚, and, it was elegant :)

Translate
SusanBrAuthor
Level 2
Apr 26, 2018 8:09 PM UTC
Kathy, Thank you so much, this works! Performance is not a huge issue. I'm not running this frequently. Susan Susan Browning Internet Testing Systems
Translate
FemkeCr
Level 4
Sep 2, 2021 8:42 AM UTC

Hi @Kathy Leeman‚ and @Nelson Soans‚ ,

I was excited to find a text mode calculation to show the week number.

Unfortunately, when I paste this code into a column in a task report or view it only returns "1" or "0".

Any suggestions for what I do wrong here?

displayname=Test

textmode=true

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

valueformat=HTML

I'm still working on my text mode skills, but this one is too complex for me...

Kind regards,

Femke

Translate
nsoans1106
Level 2
Sep 27, 2021 6:44 AM UTC

hey @Femke Crijns‚ , your formula is exactly the same as what I used in my report based on Kathy' suggestions. It worked like a charm for me but not sure why you are seeing the 1s and 0s!!

Translate
    Heather_Kulbacki
    Community Advisor
    Heather_KulbackiCommunity Advisor
    Community Advisor
    Oct 28, 2021 4:51 PM UTC

    This worked great for me - or so I thought...

    i was trying to get the FISCAL week of the year, with the fiscal year starting on 1/3

    worked great for US formatted dates, but when the user has their browser set to another language that formats the date as DD/MM/YY, it reads "1/3/" with 3 as the month and 1 as the day and flubs the formula. I changed it back to 1/1 and used some different math to account for the first week of the year date shift.

    just wanted to through that FYI out here

    Translate
    FemkeCr
    Level 4
    Oct 14, 2022 8:12 AM UTC

    Hi all,

     

    I'm still struggling with the week numbers and looking for some help.

     

    I see the week numbers now, but the week starts on Sunday in this formula. I have tried several changes in the formula, but I can't find the value to change to start a new week on Monday.

    And in 2022, the week numbers are correct (except for Mondays), but in 2023 it counts one week less than it should be.

    23 September 2023 is week 38 instead of week 37.

     

    Hopefully, someone with great textmode skills could help me out here ;-).

    Translate
    Terms & ConditionsAccessibility statement

    Loading footer...